﻿using DevExpress.Utils;
using DevExpress.XtraCharts;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using DevExpress.XtraPrinting;
using SMALLERP.DataClass;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace XJLSC.FM
{
    public partial class CBFXPT : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;

        public CBFXPT()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strSql;
            strSql = "SELECT A.Owner_Org_ROid 工厂ID,C.PLANT_NAME 工厂,ITEM_CODE 品号,ITEM_NAME 品名,ITEM_SPECIFICATION 规格,CAST(ROUND((A.CE1_CUR_LEVEL+A.CE2_CUR_LEVEL+A.CE3_CUR_LEVEL+A.CE4_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL),2) AS REAL) 单位成本,";
            strSql += " CAST(ROUND((A.CE1_CUR_LEVEL + CE1_LOW_LEVEL),2) AS REAL) 单位材料,CAST(ROUND((A.CE2_CUR_LEVEL + A.CE2_LOW_LEVEL),2) AS REAL) 单位人工,CAST(ROUND((A.CE3_CUR_LEVEL + A.CE3_LOW_LEVEL),2) AS REAL) 单位制费,CAST(ROUND((A.CE4_CUR_LEVEL + A.CE4_LOW_LEVEL),2) AS REAL) 单位加工,CAST(ROUND(ISNULL(K.DWCB, 0),2) AS REAL) 实际成本,CAST(ROUND(ISNULL(K.DWCL, 0),2) AS REAL) 实际材料,CAST(ROUND(ISNULL(K.DWRG, 0),2) AS REAL) 实际人工,CAST(ROUND(ISNULL(K.DWZF, 0),2) AS REAL) 实际制费,CAST(ROUND(ISNULL(K.DWJG, 0),2) AS REAL) 实际加工 FROM SIMULATE_COST_RESULT A LEFT JOIN ITEM B ON A.ITEM_ID = B.ITEM_BUSINESS_ID LEFT JOIN PLANT C ON A.Owner_Org_ROid = C.PLANT_ID";
            strSql += " LEFT JOIN(SELECT TRANSACTION_DATE, ITEM_ID, (SELECT ISNULL(SUM(A.COST_AMT),0)/ Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID) DWCB,(SELECT ISNULL(SUM(A.COST_AMT), 0) / Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A LEFT JOIN COST_ELEMENT B ON A.COST_ELEMENT_ID = B.COST_ELEMENT_ID WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID AND B.COST_ELEMENT_CATEGORY = '1') DWCL,(SELECT ISNULL(SUM(A.COST_AMT), 0) / Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A LEFT JOIN COST_ELEMENT B ON A.COST_ELEMENT_ID = B.COST_ELEMENT_ID WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID AND B.COST_ELEMENT_CATEGORY = '2') DWRG,(SELECT ISNULL(SUM(A.COST_AMT), 0) / Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A LEFT JOIN COST_ELEMENT B ON A.COST_ELEMENT_ID = B.COST_ELEMENT_ID WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID AND B.COST_ELEMENT_CATEGORY = '3') DWZF,(SELECT ISNULL(SUM(A.COST_AMT), 0) / Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A LEFT JOIN COST_ELEMENT B ON A.COST_ELEMENT_ID = B.COST_ELEMENT_ID WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID AND B.COST_ELEMENT_CATEGORY = '4') DWJG FROM TRANSACTION_LINE Z WHERE CATEGORY = '14' AND STOCK_ACTION = -1 AND INVENTORY_QTY> 0 AND NOT EXISTS(SELECT * FROM TRANSACTION_LINE Z1 WHERE Z1.ITEM_ID = Z.ITEM_ID AND Z1.CATEGORY = '14' AND Z1.STOCK_ACTION = -1 AND Z1.INVENTORY_QTY > 0 AND Z.TRANSACTION_DATE < Z1.TRANSACTION_DATE)) K";
            strSql += " ON K.ITEM_ID = A.ITEM_ID WHERE A.ITEM_ID = PARENT_ITEM_ID AND VERSION_TIMES = '01' AND ITEM_NAME LIKE '%" + textBox1.Text + "%'  AND ITEM_CODE LIKE '%" + textBox3.Text + "%' ";
            //textBox1.Text = strSql;
            //return;
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl1.DataSource = dt;
            }
            catch
            {

            }

            String zjph = bandedGridView1.GetFocusedRowCellValue("品号").ToString();
            String gcph = bandedGridView1.GetFocusedRowCellValue("工厂ID").ToString();
            strSql = "SELECT Z.BOM_STRUCTURE_CODE AS ID,SUBSTRING(Z.BOM_STRUCTURE_CODE,1,CASE WHEN LEN(RTRIM(Z.BOM_STRUCTURE_CODE))=6 THEN 1 ELSE LEN(RTRIM(Z.BOM_STRUCTURE_CODE))-7 END) AS ParentID,B.ITEM_CODE 品号,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格, LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))  AS R_Level,Z.BOM_STRUCTURE_CODE AS path,CAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL+CE3_CUR_LEVEL+CE4_CUR_LEVEL+CE5_CUR_LEVEL+CE6_CUR_LEVEL+CE7_CUR_LEVEL+CE8_CUR_LEVEL+CE9_CUR_LEVEL+CE10_CUR_LEVEL+CE11_CUR_LEVEL+CE12_CUR_LEVEL+CE13_CUR_LEVEL+CE14_CUR_LEVEL+CE15_CUR_LEVEL+CE16_CUR_LEVEL+CE17_CUR_LEVEL+CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((CE5_CUR_LEVEL + CE6_CUR_LEVEL + CE7_CUR_LEVEL + CE8_CUR_LEVEL + CE9_CUR_LEVEL + CE10_CUR_LEVEL + CE11_CUR_LEVEL + CE13_CUR_LEVEL + CE14_CUR_LEVEL + CE15_CUR_LEVEL + CE16_CUR_LEVEL + CE17_CUR_LEVEL + CE18_CUR_LEVEL) as real) 本阶材料,cast(CE1_CUR_LEVEL as real) 本阶人工,cast(CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL as real) 本阶制费,CAST(CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=Z.PARENT_ITEM_ID WHERE LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))<=" + textBox2.Text + " AND C.ITEM_CODE='" + zjph + "' AND VERSION_TIMES='01' ORDER BY Z.BOM_STRUCTURE_CODE";

            //textBox1.Text = strSql;
            //return;
            try
            {
                DataTable dataTable;
                dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl2.DataSource = dataTable;
                treeList1.DataSource = dataTable;
                treeList1.KeyFieldName = "ID";
                treeList1.ParentFieldName = "ParentID";
                treeList1.Columns["品号"].Visible = false;
                treeList1.Columns["R_Level"].Visible = false;
                treeList1.Columns["path"].Visible = false;
                treeList1.Columns["单位成本"].Visible = false;
                treeList1.Columns["本阶材料"].Visible = false;
                treeList1.Columns["本阶人工"].Visible = false;
                treeList1.Columns["本阶制费"].Visible = false;
                treeList1.Columns["本阶加工费"].Visible = false;
                treeList1.Columns["规格"].Visible = false;
                //treeList1.ExpandAll();
                treeList1.Refresh();
            }
            catch
            {

            }
        }

        private void gridView1_SelectionChanged(object sender, DevExpress.Data.SelectionChangedEventArgs e)
        {

        }

        private void gridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {

            string strSql;
            String zjph = bandedGridView1.GetFocusedRowCellValue("品号").ToString();
            String gcph = bandedGridView1.GetFocusedRowCellValue("工厂ID").ToString();
            strSql = "SELECT Z.BOM_STRUCTURE_CODE AS ID,SUBSTRING(Z.BOM_STRUCTURE_CODE,1,CASE WHEN LEN(RTRIM(Z.BOM_STRUCTURE_CODE))=6 THEN 1 ELSE LEN(RTRIM(Z.BOM_STRUCTURE_CODE))-7 END) AS ParentID,B.ITEM_CODE 品号,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格, LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))  AS R_Level,Z.BOM_STRUCTURE_CODE AS pathCAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL+CE3_CUR_LEVEL+CE4_CUR_LEVEL+CE5_CUR_LEVEL+CE6_CUR_LEVEL+CE7_CUR_LEVEL+CE8_CUR_LEVEL+CE9_CUR_LEVEL+CE10_CUR_LEVEL+CE11_CUR_LEVEL+CE12_CUR_LEVEL+CE13_CUR_LEVEL+CE14_CUR_LEVEL+CE15_CUR_LEVEL+CE16_CUR_LEVEL+CE17_CUR_LEVEL+CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((CE5_CUR_LEVEL + CE6_CUR_LEVEL + CE7_CUR_LEVEL + CE8_CUR_LEVEL + CE9_CUR_LEVEL + CE10_CUR_LEVEL + CE11_CUR_LEVEL + CE13_CUR_LEVEL + CE14_CUR_LEVEL + CE15_CUR_LEVEL + CE16_CUR_LEVEL + CE17_CUR_LEVEL + CE18_CUR_LEVEL) as real) 本阶材料,cast(CE1_CUR_LEVEL as real) 本阶人工,cast(CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL as real) 本阶制费,CAST(CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID  WHERE R_Level<=" + textBox2.Text + " ORDER BY R_Level,[Path]";
            //textBox1.Text = strSql;
            try
            {
                DataTable dataTable;
                dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl2.DataSource = dataTable;
                treeList1.DataSource = dataTable;
                treeList1.KeyFieldName = "ID";
                treeList1.ParentFieldName = "ParentID";
                //treeList1.ExpandAll();
                treeList1.Refresh();
            }
            catch
            {

            }

            ////新建数据源
            //DataTable dataTable = new DataTable();
            ////添加数据列
            //dataTable.Columns.Add("keyId");
            //dataTable.Columns.Add("parentId");
            //dataTable.Columns.Add("name");
            //dataTable.Columns.Add("age", typeof(double));
            //dataTable.Columns.Add("cellphone");
            //dataTable.Columns.Add("information");
            //dataTable.Columns["name"].Caption = "名称";
            //dataTable.Columns["age"].Caption = "年龄";
            //dataTable.Columns["cellphone"].Caption = "手机号";
            //dataTable.Columns["information"].Caption = "信息";
            //添加数据行
            //dataTable.Rows.Add("2DE73424-29F1-4040-C222-1936242BEC6E", "00000000-0000-0000-0000-000000000000", "A区");
            //dataTable.Rows.Add("8894B001-DB23-4BE0-4154-193625C7671A", "2DE73424-29F1-4040-C222-1936242BEC6E", "AGA120AEA06A-024", 18);
            //dataTable.Rows.Add("DBEA2178-8637-4997-C28F-19349BF5E11E", "2DE73424-29F1-4040-C222-1936242BEC6E", "汽油机组件", 20);
            //dataTable.Rows.Add("BB2", "BB0", "B区");
            //dataTable.Rows.Add("BB21", "BB2", "a", 23);
            //dataTable.Rows.Add("BB221", "BB21", "b", 25);
            //dataTable.Rows.Add("BB2211", "BB221", "b", 25);
            //dataTable.Rows.Add("BB22", "BB2", "b", 25);
            //dataTable.Rows.Add("BB23", "BB2", "c", 24);
            //dataTable.Rows.Add("BB231", "BB23", "c", 24);
            //dataTable.Rows.Add("BB2311", "BB231", "c", 24);
            //dataTable.Rows.Add("BB21", "BB231", "a", 23);

            //    //绑定数据源
            //    gridControl2.DataSource = dataTable;
            //treeList1.DataSource = dataTable;
            ////keyId表示节点本身的id
            //treeList1.KeyFieldName = "keyId";
            ////parentId表示父节点的id
            //treeList1.ParentFieldName = "parentId";


        }

        private void treeList1_AfterCheckNode(object sender, DevExpress.XtraTreeList.NodeEventArgs e)
        {
            if (e.Node == null) return;
            System.Data.DataRowView rov = treeList1.GetDataRecordByNode(e.Node) as System.Data.DataRowView;
            if (e.Node.CheckState == CheckState.Indeterminate)
            {
                e.Node.CheckState = CheckState.Checked;
            }
            if (e.Node.CheckState == CheckState.Checked)
            {
                if (e.Node.HasChildren)
                {
                    CheckNode(e.Node);
                }
                ValidParentIsChecked(e.Node);
            }
            else if (e.Node.CheckState == CheckState.Unchecked)
            {
                if (e.Node.HasChildren)
                {
                    ClearNode(e.Node);
                }
                //从根节点往下 查询 是否有打钩的子节点，如果有那么 父节点的 半选状态不变否则变为 不选择状态
                ValidParentNodeIsCanleSel(e.Node);
            }
        }

        private void CheckNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        {
            foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
            {
                if (cnode != null)
                {
                    cnode.CheckState = CheckState.Checked;
                }
                if (cnode.HasChildren)
                {
                    CheckNode(cnode);
                }
            }
        }

        private void ClearNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        {
            foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
            {
                if (cnode != null)
                {
                    cnode.CheckState = CheckState.Unchecked;
                }
                if (cnode.HasChildren)
                {
                    CheckNode(cnode);
                }
            }
        }

        private void ValidParentIsChecked(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        {
            if (node.ParentNode != null)
            {
                if (node.ParentNode.CheckState != CheckState.Checked)
                    node.ParentNode.CheckState = CheckState.Indeterminate;
                ValidParentIsChecked(node.ParentNode);
            }
        }

        private void ValidParentNodeIsCanleSel(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        {
            bool isSel = false;
            if (node.ParentNode != null)
            {
                if (node.ParentNode.CheckState == CheckState.Indeterminate)
                {//如果父节点的 状态为 半选 状态 这 更具父节点 判断子节点是否打钩
                    isSel = ValidIsHasCheckChildNode(node.ParentNode);
                    if (isSel == false)
                    {//如果所有的 子节点 都没有 “选中”那么 父节点的状态 变为 非选中状态
                        node.ParentNode.CheckState = CheckState.Unchecked;
                    }
                }
                ValidParentNodeIsCanleSel(node.ParentNode);
            }
        }

        private bool ValidIsHasCheckChildNode(DevExpress.XtraTreeList.Nodes.TreeListNode node)
        {
            bool isCheck = false;
            foreach (DevExpress.XtraTreeList.Nodes.TreeListNode cnode in node.Nodes)
            {
                if (cnode != null)
                {
                    if (cnode.CheckState == CheckState.Checked)
                    {
                        isCheck = true;
                        return isCheck;
                    }
                }
                if (cnode.HasChildren)
                {
                    isCheck = ValidIsHasCheckChildNode(cnode);
                    if (isCheck == true)
                    {
                        return isCheck;
                    }
                }
            }
            return isCheck;
        }

        private void treeList1_BeforeCheckNode(object sender, DevExpress.XtraTreeList.CheckNodeEventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl1.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl2.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void bandedGridView1_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            string strSql;
            String zjph = bandedGridView1.GetFocusedRowCellValue("品号").ToString();
            String gcph = bandedGridView1.GetFocusedRowCellValue("工厂ID").ToString();
            strSql = "SELECT Z.BOM_STRUCTURE_CODE AS ID,SUBSTRING(Z.BOM_STRUCTURE_CODE,1,CASE WHEN LEN(RTRIM(Z.BOM_STRUCTURE_CODE))=6 THEN 1 ELSE LEN(RTRIM(Z.BOM_STRUCTURE_CODE))-7 END) AS ParentID,B.ITEM_CODE 品号,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格, LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))  AS R_Level,Z.BOM_STRUCTURE_CODE AS path,CAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL+CE3_CUR_LEVEL+CE4_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL) as real) 单位成本,cast((CE1_CUR_LEVEL) as real) 本阶材料,cast(CE2_CUR_LEVEL as real) 本阶人工,cast(CE3_CUR_LEVEL as real) 本阶制费,CAST(CE4_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=Z.PARENT_ITEM_ID WHERE LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))<=" + textBox2.Text + " AND C.ITEM_CODE='" + zjph + "' AND VERSION_TIMES='01' ORDER BY Z.BOM_STRUCTURE_CODE";

            //textBox1.Text = strSql;
            try
            {
                DataTable dataTable;
                dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl2.DataSource = dataTable;
                treeList1.DataSource = dataTable;
                treeList1.KeyFieldName = "ID";
                treeList1.ParentFieldName = "ParentID";
                treeList1.Columns["品号"].Visible = false;
                treeList1.Columns["R_Level"].Visible = false;
                treeList1.Columns["path"].Visible = false;
                treeList1.Columns["单位成本"].Visible = false;
                treeList1.Columns["本阶材料"].Visible = false;
                treeList1.Columns["本阶人工"].Visible = false;
                treeList1.Columns["本阶制费"].Visible = false;
                treeList1.Columns["本阶加工费"].Visible = false;
                treeList1.Columns["规格"].Visible = false;
                //treeList1.ExpandAll();
                treeList1.Refresh();
            }
            catch
            {

            }
        }

        private void 抓取明细单价ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //string strSql;
            //String zjph = bandedGridView1.GetFocusedRowCellValue("品号").ToString();
            //String gcph = bandedGridView1.GetFocusedRowCellValue("工厂ID").ToString();
            //strSql = "SELECT R_YJ_ID AS ID,R_ZJ_ID AS ParentID,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,B.ITEM_CODE 品号,R_Level,[Path] path,CAST(ROUND((SELECT TOP 1 ISNULL((K.CE1_CUR_LEVEL+K.CE2_CUR_LEVEL+K.CE3_CUR_LEVEL+K.CE4_CUR_LEVEL+K.CE5_CUR_LEVEL+K.CE6_CUR_LEVEL+K.CE7_CUR_LEVEL+K.CE8_CUR_LEVEL+K.CE9_CUR_LEVEL+K.CE10_CUR_LEVEL+K.CE11_CUR_LEVEL+K.CE12_CUR_LEVEL+K.CE13_CUR_LEVEL+K.CE14_CUR_LEVEL+K.CE15_CUR_LEVEL+K.CE16_CUR_LEVEL+K.CE17_CUR_LEVEL+K.CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL),0) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 单位成本,";
            //strSql += "CAST(ROUND((SELECT TOP 1 ISNULL((K.CE5_CUR_LEVEL + K.CE6_CUR_LEVEL + K.CE7_CUR_LEVEL + K.CE8_CUR_LEVEL + K.CE9_CUR_LEVEL + K.CE10_CUR_LEVEL + K.CE11_CUR_LEVEL + K.CE13_CUR_LEVEL + K.CE14_CUR_LEVEL + K.CE15_CUR_LEVEL + K.CE16_CUR_LEVEL + K.CE17_CUR_LEVEL + K.CE18_CUR_LEVEL),0) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 材料,";
            //strSql += "CAST(ROUND((SELECT TOP 1 ISNULL((K.CE1_CUR_LEVEL),0) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 人工,";
            //strSql += "CAST(ROUND((SELECT TOP 1 ISNULL((K.CE2_CUR_LEVEL + K.CE3_CUR_LEVEL + K.CE4_CUR_LEVEL),0) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 制费,";
            //strSql += "CAST(ROUND((SELECT TOP 1 ISNULL((K.CE12_CUR_LEVEL),0) FROM SIMULATE_COST_RESULT K WHERE K.ITEM_ID=B.ITEM_BUSINESS_ID AND K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID AND VERSION_TIMES='01'),2) AS REAL) 加工费";
            //strSql += " FROM d_Bom1('" + zjph + "','" + gcph + "') Z LEFT JOIN ITEM A ON Z.R_ZJPH=A.ITEM_CODE LEFT JOIN ITEM B ON Z.R_YJPH=B.ITEM_CODE  WHERE R_Level<=" + textBox2.Text + " ORDER BY [Path],R_Level";

            //MessageBox.Show(strSql, "1");
            //textBox1.Text = strSql;
            //try
            //{
            //    DataTable dataTable;
            //    MessageBox.Show("0", "1");
            //    dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
            //    MessageBox.Show("1", "1");
            //    gridControl2.DataSource = dataTable;
            //    treeList1.DataSource = dataTable;
            //    treeList1.KeyFieldName = "ID";
            //    treeList1.ParentFieldName = "ParentID";
            //    treeList1.Columns["品号"].Visible = false;
            //    treeList1.Columns["R_Level"].Visible = false;
            //    treeList1.Columns["path"].Visible = false;
            //    treeList1.Columns["单位成本"].Visible = false;
            //    treeList1.Columns["材料"].Visible = false;
            //    treeList1.Columns["人工"].Visible = false;
            //    treeList1.Columns["制费"].Visible = false;
            //    treeList1.Columns["加工费"].Visible = false;
            //    treeList1.Columns["规格"].Visible = false;
            //    treeList1.ExpandAll();
            //    treeList1.Refresh();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message, "软件提示");
            //    throw ex;
            //}
            progressBar1.Visible = true;
            progressBar1.Maximum = gridView2.RowCount - 1;//设置最大长度值
            progressBar1.Value = 0;//设置当前值
            progressBar1.Step = 1;//设置没次增长多少
            for (int i = 0; i < gridView2.RowCount - 1; i++)
            {
                String strSql = "";
                String zjph = bandedGridView1.GetFocusedRowCellValue("品号").ToString();
                String yjph = gridView2.GetRowCellValue(i, "品号").ToString();
                strSql = "SELECT CAST((K.CE1_CUR_LEVEL+K.CE2_CUR_LEVEL+K.CE3_CUR_LEVEL+K.CE4_CUR_LEVEL+K.CE5_CUR_LEVEL+K.CE6_CUR_LEVEL+K.CE7_CUR_LEVEL+K.CE8_CUR_LEVEL+K.CE9_CUR_LEVEL+K.CE10_CUR_LEVEL+K.CE11_CUR_LEVEL+K.CE12_CUR_LEVEL+K.CE13_CUR_LEVEL+K.CE14_CUR_LEVEL+K.CE15_CUR_LEVEL+K.CE16_CUR_LEVEL+K.CE17_CUR_LEVEL+K.CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((K.CE5_CUR_LEVEL + K.CE6_CUR_LEVEL + K.CE7_CUR_LEVEL + K.CE8_CUR_LEVEL + K.CE9_CUR_LEVEL + K.CE10_CUR_LEVEL + K.CE11_CUR_LEVEL + K.CE13_CUR_LEVEL + K.CE14_CUR_LEVEL + K.CE15_CUR_LEVEL + K.CE16_CUR_LEVEL + K.CE17_CUR_LEVEL + K.CE18_CUR_LEVEL) as real) 本阶材料,cast(K.CE1_CUR_LEVEL as real) 本阶人工,cast(K.CE2_CUR_LEVEL + K.CE3_CUR_LEVEL + K.CE4_CUR_LEVEL as real) 本阶制费,CAST(K.CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT K LEFT JOIN ITEM A ON K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON K.ITEM_ID=B.ITEM_BUSINESS_ID  WHERE A.ITEM_CODE='" + zjph + "' AND B.ITEM_CODE='" + yjph + "'  AND VERSION_TIMES='01'";
                try
                {
                    sdr1 = db.GetDataReader1(strSql);
                    while (sdr1.Read())
                    {
                        //MessageBox.Show(sdr1["单位成本"].ToString(),"系统提示");
                        gridView2.SetRowCellValue(i, "单位成本", sdr1["单位成本"].ToString());
                        gridView2.SetRowCellValue(i, "本阶材料", sdr1["本阶材料"].ToString());
                        gridView2.SetRowCellValue(i, "本阶人工", sdr1["本阶人工"].ToString());
                        gridView2.SetRowCellValue(i, "本阶制费", sdr1["本阶制费"].ToString());
                        gridView2.SetRowCellValue(i, "本阶加工费", sdr1["本阶加工费"].ToString());
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    throw ex;
                }
                finally
                {
                    sdr1.Close();
                }

                progressBar1.Value += progressBar1.Step;
            }
            progressBar1.Visible = false;

        }

        private void gridControl1_Click(object sender, EventArgs e)
        {

        }

        private void repositoryItemTextEdit1_ParseEditValue(object sender, DevExpress.XtraEditors.Controls.ConvertEditValueEventArgs e)
        {
            TextEdit edit = (TextEdit)sender;
            if (edit.Text == string.Empty)
                e.Value = null;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string strSql = "";
            strSql = "declare @sql1 VARCHAR(8000) set @sql1 = 'SELECT DISTINCT B.ITEM_CODE 品号,B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格' select @sql1 = @sql1 + ' , ROUND(sum(case ACCOUNT_YEAR+RIGHT(''0''+RTRIM(ACCOUNT_PERIOD_SEQNO),2) when ''' + TY003 + ''' then CAST(ROUND(A.UNIT_COST,2) AS REAL) else 0 end),2) [' + TY003 + ']' from(SELECT DISTINCT SUBSTRING(RQ001,1,6) AS TY003 FROM CMSRQ WHERE RQ001 >= CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker1.Value.ToShortDateString() + "'), 112) AND RQ001 <= CONVERT(CHAR(8), DATEADD(DAY, 0, '" + dateTimePicker2.Value.ToShortDateString() + "'), 112)) as a set @sql1 = @sql1 + ' FROM INV_UNIT_COST A LEFT JOIN ITEM B ON A.ITEM_ID=B.ITEM_BUSINESS_ID  group by B.ITEM_CODE,B.ITEM_NAME,B.ITEM_SPECIFICATION' EXEC(@sql1)";
            //textBox5.Text = strSql;
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                this.gridControl3.DataSource = dt;
                gridView3.OptionsView.EnableAppearanceEvenRow = true;
                gridView3.OptionsView.EnableAppearanceOddRow = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl3.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        #region   图表基础
        /// <summary>
        /// 创建图表
        /// </summary>
        /// <param name="titleName">图表的标题名称</param>
        /// <param name="chartControlName">图表的名称</param>
        /// <param name="initLocation">图表的初始位置</param>
        /// <param name="InitSize">图表的初始大小</param>
        /// <returns></returns>
        private ChartControl CreateChart(string titleName, string chartControlName,
            System.Drawing.Point initLocation, System.Drawing.Size InitSize)
        {
            ChartControl chartControl = new ChartControl();
            chartControl.Legend.Name = "Default Legend";
            chartControl.Location = initLocation;
            chartControl.Name = chartControlName;
            chartControl.Size = InitSize;
            chartControl.SeriesSerializable = new DevExpress.XtraCharts.Series[0];

            //添加标题
            if (chartControl.Titles.Count < 1)
            {
                chartControl.Titles.Add(new ChartTitle());
                chartControl.Titles[0].Text = titleName;
            }

            Controls.Add(chartControl);
            chartControl.Show();

            return chartControl;
        }
        /// <summary>
        /// 清空图表
        /// </summary>
        /// <param name="chartControl">图表</param>
        private void ClearChart(ChartControl chartControl)
        {
            if (chartControl != null)
            {
                this.Controls.Remove(chartControl);
                chartControl = null;
                chartControl?.Dispose();
            }

        }

        /// <summary>
        /// 填充数据到线性图表
        /// </summary>
        /// <param name="seriesName">图例类型名称</param>
        /// <param name="curSeriesAllDataDic">图例类型对应的所有数据</param>
        /// <param name="chartControl">线性图表</param>
        /// <param name="viewType">线性图表类型</param>
        private void FillDataToLineChart(string seriesName,
            Dictionary<string, double> curSeriesAllDataDic,
            ChartControl chartControl, ViewType viewType = ViewType.Line)
        {
            if (chartControl == null ||
                curSeriesAllDataDic == null && curSeriesAllDataDic.Count == 0) return;


            //添加一个类型的数据
            Series series = new Series(seriesName, viewType);

            foreach (var item in curSeriesAllDataDic)
            {
                MessageBox.Show(item.Value.ToString(), item.Key);
                series.Points.Add(new SeriesPoint(item.Key, item.Value));
            }
            chartControl.Series.Add(series);
            //图例的样式设置
            ((LineSeriesView)series.View).MarkerVisibility = DevExpress.Utils.DefaultBoolean.True;
            ((LineSeriesView)series.View).LineMarkerOptions.Kind = MarkerKind.Circle;
            ((LineSeriesView)series.View).LineStyle.DashStyle = DashStyle.Solid;
        }

        /// <summary>
        /// 图表设置
        /// </summary>
        /// <param name="chartControl">chartControl图表</param>
        /// <param name="axisXName">X轴名称</param>
        /// <param name="axisYName">Y轴名称</param>
        private void ChartSettings(ChartControl chartControl, string axisXName, string axisYName)
        {
            //显示图例复选框
            chartControl.Legend.MarkerMode = DevExpress.XtraCharts.LegendMarkerMode.CheckBoxAndMarker;

            XYDiagram diagram = (XYDiagram)chartControl.Diagram;

            if (diagram != null)
            {
                //允许缩放X轴
                diagram.EnableAxisXZooming = true;
                diagram.AxisX.Title.Visibility = DevExpress.Utils.DefaultBoolean.True;
                diagram.AxisX.Title.Alignment = StringAlignment.Far;
                diagram.AxisX.Title.Text = axisXName;
                //要开启滚动条需设置自动范围为false
                diagram.AxisX.VisualRange.Auto = false;
                //启用X轴滚动条
                diagram.EnableAxisXScrolling = true;
                //在不拉动滚动条的时候，X轴显示的数据个数（固定X轴的长度）
                //diagram.AxisX.WholeRange.SetMinMaxValues("2021/08/01", "2021/08/07");

                // Define the whole range for the Y-axis. 
                diagram.AxisY.WholeRange.Auto = false;
                diagram.AxisY.WholeRange.SetMinMaxValues(10, 70);


                //允许缩放Y轴
                diagram.EnableAxisYZooming = true;
                diagram.AxisY.Title.Visibility = DevExpress.Utils.DefaultBoolean.True;
                diagram.AxisY.Title.Alignment = StringAlignment.Far;
                diagram.AxisY.Title.Text = axisYName;
                //chartControl.CrosshairOptions.ShowArgumentLabels = true;
                //chartControl.CrosshairOptions.ShowValueLine = true;
            }
        }

        //初始化图表
        private void InitChartSettings(ChartControl chartControl, string axisXName, string axisYName)
        {
            XYDiagram diagram = (XYDiagram)chartControl.Diagram;

            if (diagram != null)
            {
                //允许缩放X轴
                diagram.EnableAxisXZooming = true;
                diagram.AxisX.Title.Visibility = DevExpress.Utils.DefaultBoolean.True;
                diagram.AxisX.Title.Alignment = StringAlignment.Far;
                diagram.AxisX.Title.Text = axisXName;
                //要开启滚动条需设置自动范围为false
                diagram.AxisX.VisualRange.Auto = false;
                //启用X轴滚动条
                diagram.EnableAxisXScrolling = true;

                //在不拉动滚动条的时候，X轴显示的数据个数（固定X轴的长度）
                diagram.AxisX.WholeRange.SetMinMaxValues(Convert.ToDateTime("2021/08/01"), Convert.ToDateTime("2021/08/07"));

                // Define the whole range for the Y-axis. 
                diagram.AxisY.WholeRange.Auto = false;
                diagram.AxisY.WholeRange.SetMinMaxValues(10, 70);


                //允许缩放Y轴
                diagram.EnableAxisYZooming = true;
                diagram.AxisY.Title.Visibility = DevExpress.Utils.DefaultBoolean.True;
                diagram.AxisY.Title.Alignment = StringAlignment.Far;
                diagram.AxisY.Title.Text = axisYName;

                //chartControl.CrosshairOptions.ShowArgumentLabels = true;
                //chartControl.CrosshairOptions.ShowValueLine = true;
            }
        }
        #endregion

        private void gridView3_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            ////MessageBox.Show("1234","系统提示");

            ////0-清空图表
            //ClearChart(chartControl1);

            ////1-创建图表
            //chartControl1 = CreateChart("线性图表", "LineChart", new Point(10, 12), new Size(600, 360));

            ///*2-填充数据到图表*/
            ////模拟数据
            //Dictionary<string, Dictionary<string, double>> needFillDataDic =
            //    new Dictionary<string, Dictionary<string, double>>();

            //Dictionary<string, double> tmpADic = new Dictionary<string, double>();
            //tmpADic.Add("2021/08/01", 36.5);
            //tmpADic.Add("2021/08/02", 22.3);
            //tmpADic.Add("2021/08/03", 21.1);
            //tmpADic.Add("2021/08/04", 25.8);
            //tmpADic.Add("2021/08/05", 32.3);
            //tmpADic.Add("2021/08/06", 42.0);
            //tmpADic.Add("2021/08/07", 35.5);
            //needFillDataDic.Add("类型A", tmpADic);

            //Dictionary<string, double> tmpBDic = new Dictionary<string, double>();
            //tmpBDic.Add("2021/08/01", 66.5);
            //tmpBDic.Add("2021/08/02", 51.2);
            //tmpBDic.Add("2021/08/03", 44.6);
            //tmpBDic.Add("2021/08/04", 35.8);
            //tmpBDic.Add("2021/08/05", 42.3);
            //tmpBDic.Add("2021/08/06", 32.0);
            //tmpBDic.Add("2021/08/07", 55.5);
            //needFillDataDic.Add("类型B", tmpBDic);


            ////填充数据
            //if (needFillDataDic != null && needFillDataDic.Count > 0)
            //{
            //    foreach (var item in needFillDataDic)
            //    {
            //        string seriesName = item.Key;
            //        Dictionary<string, double> curSeriesDataDic = item.Value;

            //        FillDataToLineChart(seriesName, curSeriesDataDic, chartControl1);

            //    }
            //}


            ////3-图表设置
            //ChartSettings(chartControl1, "年月", "单位成本（单位：元）");
            //InitChartSettings(chartControl1, "年月", "成本");

            //ChartControl chartControl1 = new ChartControl();
            //chartControl1.Parent = this;
            //chartControl1.Dock = DockStyle.Fill;
            ///
            ///XYDiagram对象：xyDiagram1
            ///
            XYDiagram xyDiagram1 = new XYDiagram();

            ///
            ///XYDiagramPane对象：xyDiagramPane1
            ///
            //XYDiagramPane xyDiagramPane1 = new XYDiagramPane();
            //XYDiagramPane xyDiagramPane2 = new XYDiagramPane();
            ///
            ///将XYDiagramPane对象：xyDiagramPane1，添加到XYDi中agram对象：xyDiagram1
            ///
            //xyDiagram1.Panes.AddRange(new XYDiagramPane[] { xyDiagramPane1, xyDiagramPane2 });
            ///
            ///Series对象：series1
            ///
            //chartControl1.Series.Clear();
            Series series1 = new Series("单位成本", ViewType.Bar);
            //Series series2 = new Series();
            //Series series3 = new Series();
            ///
            ///将Series对象：series1，添加到ChartControl对象：chartControl1
            ///
            //chartControl1.SeriesSerializable = new Series[] { series1};
            ///
            ///xxxSeriesView系列视图对象：lineSeriesView1
            ///

            //LineSeriesView lineSeriesView1 = new LineSeriesView();
            //LineSeriesView lineSeriesView2 = new LineSeriesView();
            //LineSeriesView lineSeriesView3 = new LineSeriesView();
            ///
            ///将xxxSeriesView系列视图对象：lineSeriesView1，绑定到对应的系列对象中，即指定系列的系列视图类型
            ///

            //模拟数据
            //Dictionary<string, Dictionary<string, double>> needFillDataDic =
            //    new Dictionary<string, Dictionary<string, double>>();

            //Dictionary<string, double> tmpADic = new Dictionary<string, double>();
            //tmpADic.Add("2021/08/01", 36.5);
            //tmpADic.Add("2021/08/02", 22.3);
            //tmpADic.Add("2021/08/03", 21.1);
            //tmpADic.Add("2021/08/04", 25.8);
            //tmpADic.Add("2021/08/05", 32.3);
            //tmpADic.Add("2021/08/06", 42.0);
            //tmpADic.Add("2021/08/07", 35.5);
            //needFillDataDic.Add("series1", tmpADic);

            //Dictionary<string, double> tmpBDic = new Dictionary<string, double>();
            //tmpBDic.Add("2021/08/01", 66.5);
            //tmpBDic.Add("2021/08/02", 51.2);
            //tmpBDic.Add("2021/08/03", 44.6);
            //tmpBDic.Add("2021/08/04", 35.8);
            //tmpBDic.Add("2021/08/05", 42.3);
            //tmpBDic.Add("2021/08/06", 32.0);
            //tmpBDic.Add("2021/08/07", 55.5);
            //needFillDataDic.Add("类型B", tmpBDic);

            //series1.Points.Add(new SeriesPoint("2021/08/01", 36.5));
            //series1.Points.Add(new SeriesPoint("2021/08/02", 22.3));
            //series1.Points.Add(new SeriesPoint("2021/08/03", 21.1));
            //series1.Points.Add(new SeriesPoint("2021/08/04", 25.8));
            //series1.Points.Add(new SeriesPoint("2021/08/05", 32.3));
            //series1.Points.Add(new SeriesPoint("2021/08/06", 42.0));
            //series1.Points.Add(new SeriesPoint("2021/08/07", 35.5));
            DataTable dt = new DataTable();
            DataColumn date1 = new DataColumn();
            date1.DataType = typeof(string);
            date1.Caption = "NY";
            date1.ColumnName = "NY";
            dt.Columns.Add(date1);

            DataColumn data1 = new DataColumn();
            data1.DataType = typeof(Double);
            data1.Caption = "DWCB";
            data1.ColumnName = "DWCB";
            dt.Columns.Add(data1);

            for (int i = 3; i <= gridView3.Columns.Count - 1; i++)
            {
                DataRow dr = dt.NewRow();
                dt.Rows.Add(dr);
                dr[0] = gridView3.Columns[i].FieldName;
                dr[1] = Convert.ToDouble(gridView3.GetFocusedRowCellValue(gridView3.Columns[i].FieldName).ToString());
            }
            Series Series1 = new Series("单位成本", ViewType.Bar);
            Series1.DataSource = dt;
            Series1.ArgumentScaleType = ScaleType.Qualitative;
            Series1.ArgumentDataMember = "NY";
            Series1.ValueScaleType = ScaleType.Numerical;
            Series1.ValueDataMembers.AddRange(new string[] { "DWCB" });
            chartControl1.Series.Add(Series1);

            //chartControl1.Series.Add(series1);

            //填充数据
            //if (needFillDataDic != null && needFillDataDic.Count > 0)
            //{
            //    foreach (var item in needFillDataDic)
            //    {
            //        string seriesName = item.Key;
            //        Dictionary<string, double> curSeriesDataDic = item.Value;

            //        FillDataToLineChart(seriesName, curSeriesDataDic, chartControl1);
            //    }
            //    chartControl1.Series.Add(series1);
            //}
            //chartControl1.Series.Add(series1);
            //图例的样式设置
            //try
            //{
            //((LineSeriesView)series1.View).MarkerVisibility = DevExpress.Utils.DefaultBoolean.True;
            //((LineSeriesView)series1.View).LineMarkerOptions.Kind = MarkerKind.Circle;
            //((LineSeriesView)series1.View).LineStyle.DashStyle = DashStyle.Solid;
            //}
            //catch
            //{

            //}

            //series1.View = lineSeriesView1;
            //series2.View = lineSeriesView2;
            //series3.View = lineSeriesView3;
            ///
            ///以下是必不可少的，如果没有，chartControl1.Diagram = xyDiagram1;语句会报错
            ///语句数量由：ChartControl对象个数、Series对象个数、xxxSeriesView系列视图对象个数决定
            ///
            ((System.ComponentModel.ISupportInitialize)(chartControl1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(xyDiagram1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(xyDiagramPane1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(xyDiagramPane2)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(series1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(series2)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView2)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(series3)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView3)).BeginInit();
            ///
            ///将XYDiagramPane对象：xyDiagramPane1，添加到ChartControl对象：chartControl1中
            ///
            //MessageBox.Show("1","系统提示");
            //chartControl1.Refresh();
            //chartControl1.Diagram = xyDiagram1;

            //MessageBox.Show("2", "系统提示");

            ///
            ///为具体的XY图表板添加具体的数据，即让指定的Series对象显示在具体的XY图面板中
            ///
            //xyDiagramPane1.Name = "Pane_1";
            //xyDiagramPane2.Name = "Pane_2";
            //lineSeriesView2.PaneName = xyDiagramPane1.Name;
            //lineSeriesView3.PaneName = xyDiagramPane2.Name;

        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (chartControl1 == null)
            {
                MessageBox.Show("图表为空！导出失败", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Title = "导出Excel";
            saveFileDialog.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string filter = saveFileDialog.FileName.Substring(saveFileDialog.FileName.LastIndexOf(".") + 1);
                if (filter == "xls")
                {
                    XlsExportOptions options = new XlsExportOptions();
                    options.TextExportMode = TextExportMode.Value;
                    options.ExportMode = XlsExportMode.SingleFile;
                    chartControl1.ExportToXls(saveFileDialog.FileName, options);
                }
                else
                {
                    XlsxExportOptions options = new XlsxExportOptions();
                    options.TextExportMode = TextExportMode.Value;
                    options.ExportMode = XlsxExportMode.SingleFile;
                    chartControl1.ExportToXlsx(saveFileDialog.FileName, options);
                }
                MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                if (MessageBox.Show("保存成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {
                    System.Diagnostics.Process.Start(saveFileDialog.FileName);
                }
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            XYDiagram xyDiagram1 = new XYDiagram();

            ///
            ///XYDiagramPane对象：xyDiagramPane1
            ///
            //XYDiagramPane xyDiagramPane1 = new XYDiagramPane();
            //XYDiagramPane xyDiagramPane2 = new XYDiagramPane();
            ///
            ///将XYDiagramPane对象：xyDiagramPane1，添加到XYDi中agram对象：xyDiagram1
            ///
            //xyDiagram1.Panes.AddRange(new XYDiagramPane[] { xyDiagramPane1, xyDiagramPane2 });
            ///
            ///Series对象：series1
            ///
            Series series1 = new Series();
            //Series series2 = new Series();
            //Series series3 = new Series();
            ///
            ///将Series对象：series1，添加到ChartControl对象：chartControl1
            ///
            chartControl1.SeriesSerializable = new Series[] { series1 };
            ///
            ///xxxSeriesView系列视图对象：lineSeriesView1
            ///
            //LineSeriesView lineSeriesView1 = new LineSeriesView();
            //LineSeriesView lineSeriesView2 = new LineSeriesView();
            //LineSeriesView lineSeriesView3 = new LineSeriesView();
            ///
            ///将xxxSeriesView系列视图对象：lineSeriesView1，绑定到对应的系列对象中，即指定系列的系列视图类型
            ///

            //模拟数据
            Dictionary<string, Dictionary<string, double>> needFillDataDic =
                new Dictionary<string, Dictionary<string, double>>();

            Dictionary<string, double> tmpADic = new Dictionary<string, double>();
            tmpADic.Add("2021/08/01", 36.5);
            tmpADic.Add("2021/08/02", 22.3);
            tmpADic.Add("2021/08/03", 21.1);
            tmpADic.Add("2021/08/04", 25.8);
            tmpADic.Add("2021/08/05", 32.3);
            tmpADic.Add("2021/08/06", 42.0);
            tmpADic.Add("2021/08/07", 35.5);
            needFillDataDic.Add("单位成本", tmpADic);

            //Dictionary<string, double> tmpBDic = new Dictionary<string, double>();
            //tmpBDic.Add("2021/08/01", 66.5);
            //tmpBDic.Add("2021/08/02", 51.2);
            //tmpBDic.Add("2021/08/03", 44.6);
            //tmpBDic.Add("2021/08/04", 35.8);
            //tmpBDic.Add("2021/08/05", 42.3);
            //tmpBDic.Add("2021/08/06", 32.0);
            //tmpBDic.Add("2021/08/07", 55.5);
            //needFillDataDic.Add("类型B", tmpBDic);


            //填充数据
            if (needFillDataDic != null && needFillDataDic.Count > 0)
            {
                foreach (var item in needFillDataDic)
                {
                    string seriesName = item.Key;
                    Dictionary<string, double> curSeriesDataDic = item.Value;

                    FillDataToLineChart(seriesName, curSeriesDataDic, chartControl1);


                }
            }
            //chartControl1.Series.Add(series1);
            //图例的样式设置
            //try
            //{
            ((LineSeriesView)series1.View).MarkerVisibility = DevExpress.Utils.DefaultBoolean.True;
            ((LineSeriesView)series1.View).LineMarkerOptions.Kind = MarkerKind.Circle;
            ((LineSeriesView)series1.View).LineStyle.DashStyle = DashStyle.Solid;
            //}
            //catch
            //{

            //}

            //series1.View = lineSeriesView1;
            //series2.View = lineSeriesView2;
            //series3.View = lineSeriesView3;
            ///
            ///以下是必不可少的，如果没有，chartControl1.Diagram = xyDiagram1;语句会报错
            ///语句数量由：ChartControl对象个数、Series对象个数、xxxSeriesView系列视图对象个数决定
            ///
            ((System.ComponentModel.ISupportInitialize)(chartControl1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(xyDiagram1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(xyDiagramPane1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(xyDiagramPane2)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(series1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView1)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(series2)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView2)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(series3)).BeginInit();
            //((System.ComponentModel.ISupportInitialize)(lineSeriesView3)).BeginInit();
            ///
            ///将XYDiagramPane对象：xyDiagramPane1，添加到ChartControl对象：chartControl1中
            ///

            chartControl1.Diagram = xyDiagram1;
            //.Show("1","123");
            ///
            ///为具体的XY图表板添加具体的数据，即让指定的Series对象显示在具体的XY图面板中
            ///
            //xyDiagramPane1.Name = "Pane_1";
            //xyDiagramPane2.Name = "Pane_2";
            //lineSeriesView2.PaneName = xyDiagramPane1.Name;
            //lineSeriesView3.PaneName = xyDiagramPane2.Name;
        }

        private void button8_Click(object sender, EventArgs e)
        {
            //tabPage3.Show();
            tabControl1.SelectedIndex = 2;
            DataTable dt = new DataTable();
            String phs = "";
            int[] rows = this.bandedGridView1.GetSelectedRows();
            foreach (int row in rows)
            {

                String pm = this.bandedGridView1.GetRowCellValue(row, "品名").ToString();
                String ph = this.bandedGridView1.GetRowCellValue(row, "品号").ToString();
                if (phs == "")
                {
                    phs = ph;
                }
                else
                {
                    phs += "," + ph;
                }
            }

            String strSql = "SELECT DISTINCT LEFT(A.ITEM_CODE,1) 阶次,RTRIM(A.UDF023) 类型,'' 品名,'' 规格,'' A型号,'' B型号,0.00 差异 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM A ON Z.ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON Z.PARENT_ITEM_ID=B.ITEM_BUSINESS_ID WHERE  LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))<=2 AND '" + phs + "' LIKE '%' + RTRIM(B.ITEM_CODE) + '%' AND Z.VERSION_TIMES='01'  ORDER BY LEFT(A.ITEM_CODE,1),RTRIM(A.UDF023)"; //AND RTRIM(A.UDF023)<> ''
            //textBox1.Text = strSql;
            //return;
            try
            {
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl4.DataSource = dt;
                int[] rws = this.bandedGridView1.GetSelectedRows();
                int k = 0;
                foreach (int row in rws)
                {
                    k++;
                    String pm = this.bandedGridView1.GetRowCellValue(row, "品名").ToString();
                    String ph = this.bandedGridView1.GetRowCellValue(row, "品号").ToString();
                    if (phs == "")
                    {
                        phs = ph;
                    }
                    else
                    {
                        phs += "," + ph;
                    }
                    if (k == 1)
                    {
                        gridView4.Columns["A型号"].Caption = pm;
                        gridView4.Columns["A型号"].Tag = ph;
                    }
                    if (k == 2)
                    {
                        gridView4.Columns["B型号"].Caption = pm;
                        gridView4.Columns["B型号"].Tag = ph;
                    }
                    //DataColumn data = new DataColumn();
                    //data.DataType = typeof(String);
                    //data.Caption = pm;
                    //data.ColumnName = ph;
                    //dt.Columns.Add(data);
                }
                //gridControl4.DataSource = dt;
                //gridView4.Columns["ITEM_ID"].Visible = false;
            }
            catch
            {

            }


            //int[] ros = this.bandedGridView1.GetSelectedRows();
            //foreach (int row in ros)
            //{
            //    try
            //    {
            //        String pm = this.bandedGridView1.GetRowCellValue(row, "品名").ToString();
            //        String ph = this.bandedGridView1.GetRowCellValue(row, "品号").ToString();
            //        gridView4.Columns[ph].Caption = pm;
            //        gridView4.Columns[ph].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            //        gridView4.Columns[ph].SummaryItem.Mode = DevExpress.Data.SummaryMode.AllRows;
            //    }
            //    catch
            //    {

            //    }
            //}


            for (int j = gridView4.Columns["A型号"].ColumnHandle - 1; j <= gridView4.Columns.Count - 2; j++)
            {
                progressBar2.Visible = true;
                progressBar2.Value = 0;//设置当前值
                progressBar2.Step = 1;//设置没次增长多少
                progressBar2.Maximum = gridView4.RowCount;
                for (int i = 0; i <= gridView4.RowCount - 1; i++)
                {
                    progressBar2.Value += progressBar2.Step;
                    String zjph = gridView4.Columns[j].Tag.ToString();
                    //MessageBox.Show(zjph, "123");
                    String yjph = gridView4.GetRowCellValue(i, "类型").ToString();
                    strSql = "SELECT B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,CAST((K.CE1_CUR_LEVEL+K.CE2_CUR_LEVEL+K.CE3_CUR_LEVEL+K.CE4_CUR_LEVEL+K.CE5_CUR_LEVEL+K.CE6_CUR_LEVEL+K.CE7_CUR_LEVEL+K.CE8_CUR_LEVEL+K.CE9_CUR_LEVEL+K.CE10_CUR_LEVEL+K.CE11_CUR_LEVEL+K.CE12_CUR_LEVEL+K.CE13_CUR_LEVEL+K.CE14_CUR_LEVEL+K.CE15_CUR_LEVEL+K.CE16_CUR_LEVEL+K.CE17_CUR_LEVEL+K.CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((K.CE5_CUR_LEVEL + K.CE6_CUR_LEVEL + K.CE7_CUR_LEVEL + K.CE8_CUR_LEVEL + K.CE9_CUR_LEVEL + K.CE10_CUR_LEVEL + K.CE11_CUR_LEVEL + K.CE13_CUR_LEVEL + K.CE14_CUR_LEVEL + K.CE15_CUR_LEVEL + K.CE16_CUR_LEVEL + K.CE17_CUR_LEVEL + K.CE18_CUR_LEVEL) as real) 本阶材料,cast(K.CE1_CUR_LEVEL as real) 本阶人工,cast(K.CE2_CUR_LEVEL + K.CE3_CUR_LEVEL + K.CE4_CUR_LEVEL as real) 本阶制费,CAST(K.CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT K LEFT JOIN ITEM A ON K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON K.ITEM_ID=B.ITEM_BUSINESS_ID  WHERE A.ITEM_CODE='" + zjph + "' AND RTRIM(B.UDF023)='" + yjph + "'  AND VERSION_TIMES='01'";
                    //MessageBox.Show(strSql,"系统提示");
                    try
                    {
                        sdr1 = db.GetDataReader1(strSql);
                        while (sdr1.Read())
                        {
                            Double bjtr = Convert.ToDouble(sdr1["本阶材料"].ToString()) + Convert.ToDouble(sdr1["本阶人工"].ToString()) + Convert.ToDouble(sdr1["本阶制费"].ToString()) + Convert.ToDouble(sdr1["本阶加工费"].ToString());
                            Double dwcb = Convert.ToDouble(sdr1["单位成本"].ToString());
                            gridView4.SetRowCellValue(i, gridView4.Columns[j].FieldName.ToString(), dwcb.ToString("0.##"));
                            gridView4.SetRowCellValue(i, "品名", sdr1["品名"].ToString());
                            gridView4.SetRowCellValue(i, "规格", sdr1["规格"].ToString());
                            //gridView2.SetRowCellValue(i, "本阶材料", sdr1["本阶材料"].ToString());
                            //gridView2.SetRowCellValue(i, "本阶人工", sdr1["本阶人工"].ToString());
                            //gridView2.SetRowCellValue(i, "本阶制费", sdr1["本阶制费"].ToString());
                            //gridView2.SetRowCellValue(i, "本阶加工费", sdr1["本阶加工费"].ToString());
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "软件提示");
                        throw ex;
                    }
                    finally
                    {
                        sdr1.Close();
                    }
                }
                progressBar2.Visible = false;
            }
            for (int i = 0; i <= gridView4.RowCount - 1; i++)
            {
                try
                {
                    Double bja = Convert.ToDouble(gridView4.GetRowCellValue(i, "A型号"));
                    Double bjb = Convert.ToDouble(gridView4.GetRowCellValue(i, "B型号"));
                    Double bjc = Math.Round((bja - bjb), 2);
                    gridView4.SetRowCellValue(i, "差异", bjc);
                }
                catch
                {
                }
            }

        }

        private void button7_Click_1(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl4.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                //gridControl5.ExportToXls(fileDialog.FileName);
                gridControl5.ExportToXlsx(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            string strSql;
            string str = ("0" + comboBox2.Text);
            string str1 = str.Substring(str.Length - 2, 2);
            //MessageBox.Show(str1, "系统提示");
            String bbny = comboBox1.Text + str1;

            strSql = "drop table #TEMP_ZTJE";
            try
            {

                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch 
            {
               
            }


            String strSql0 = "";
            strSql0 = "SELECT * INTO #TEMP_ZTJE FROM (";
            strSql0 += "SELECT A.MO_ID,COST_ELEMENT.COST_ELEMENT_ID,SUM(C1.OPENING_WIPEU_QTY*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 1 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END)) QCJE,SUM(C1.OPENING_WIPEU_QTY) OPENING_WIPEU_QTY,SUM(C1.ENDING_WIPEU_QTY*(CASE WHEN (ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0))=0 THEN 1 ELSE (ISNULL(Z1.COST_AMT,0)+ISNULL(Z3.COST_AMT,0))/(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0)) END)) QMJE,SUM(C1.ENDING_WIPEU_QTY) ENDING_WIPEU_QTY";
            strSql0 += " FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=C.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN (SELECT A.MO_ID,Z.ITEM_ID,OPENING_WIPEU_QTY,ISSUED_QTY,OPENING_WIPEU_QTY+ISSUED_QTY-ENDING_WIPEU_QTY BQZC,ENDING_WIPEU_QTY FROM COST_MOMAT_WIPEU_D Z LEFT JOIN COST_MOMAT_WIPEU A ON A.COST_MOMAT_WIPEU_ID=Z.COST_MOMAT_WIPEU_ID  WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_CODE='" + comboBox2.Text + "') C1 ON C1.MO_ID=A.MO_ID ";
            strSql0 += " LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY*STOCK_ACTION) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT*STOCK_ACTION) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE  EXISTS(SELECT * FROM COST_DOC G1 WHERE G1.MO_ID=A1.MO_ID AND G1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND G1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z1 ON Z1.MO_ID=C1.MO_ID AND C1.ITEM_ID=Z1.ITEM_ID LEFT JOIN ITEM G ON G.ITEM_BUSINESS_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY) FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z2 ON Z2.MO_ID=A.MO_ID  LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z3 ON Z3.MO_ID=A.MO_ID AND Z3.ITEM_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D .REFERENCE_SOURCE_ID_ROid,RECEIPTED_BUSINESS_QTY FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "') Z4 ON Z4.MO_ID=A.MO_ID";
            strSql0 += " LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN FEATURE_GROUP H ON H.FEATURE_GROUP_ID=G.FEATURE_GROUP_ID LEFT JOIN ITEM_FINANCIAL ON C1.ITEM_ID=ITEM_FINANCIAL.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID LEFT JOIN COST_ELEMENT ON IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID=COST_ELEMENT.COST_ELEMENT_ID WHERE C.DOC_NO LIKE '%" + textBox6.Text + "%' AND A.ACCOUNT_YEAR='" + comboBox1.Text + "' AND A.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' GROUP BY A.MO_ID,COST_ELEMENT.COST_ELEMENT_ID";          
            strSql0 += ") A";
            //textBox6.Text = strSql0;
            //return;
            //try
            //{

            //    if (db.ExecDataBySql1(strSql) > 0)
            //    {

            //    }
            //    else
            //    {

            //    }
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message, "软件提示");
            //    throw ex;
            //}
            if (comboBox3.Text == "厂内")
            {
                strSql = "SELECT isnull(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,0 完工金额,0 未完工金额,'材料' 费用类型,ISNULL(H.FEATURE_GROUP_NAME,'') 材料类型,ISNULL(G.ITEM_CODE,L.COST_ELEMENT_CODE) 材料品号,ISNULL(G.ITEM_NAME,L.COST_ELEMENT_NAME) 材料品名,ISNULL(G.ITEM_SPECIFICATION,'') 材料规格,CAST(C1.OPENING_WIPEU_QTY AS REAL) 期初数量,CAST(ROUND(ISNULL((CASE WHEN K.QCJE=0 THEN (CASE WHEN K.OPENING_WIPEU_QTY=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.OPENING_COST_AMT/K.OPENING_WIPEU_QTY*C1.OPENING_WIPEU_QTY END) ELSE (COST_DOC_D.OPENING_COST_AMT/K.QCJE)*C1.OPENING_WIPEU_QTY*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 1 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END) END),(CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END)),2) AS REAL) 期初金额,0 本阶数量,0 本阶金额,CAST(ROUND(ISNULL(Z3.ACTUAL_INVENTORY_QTY,0),2) AS REAL) 下阶数量,CAST(ISNULL(Z3.COST_AMT,0) AS REAL) 下阶金额, CAST(C1.ENDING_WIPEU_QTY AS REAL) 期末数量,";
                strSql += " CAST(ROUND(ISNULL((CASE WHEN ISNULL(K.QMJE,0)=0 THEN (CASE WHEN ISNULL(K.ENDING_WIPEU_QTY,0)=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.ENDING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.ENDING_COST_AMT/K.ENDING_WIPEU_QTY*C1.ENDING_WIPEU_QTY END) ELSE (COST_DOC_D.ENDING_COST_AMT/K.QMJE)*C1.ENDING_WIPEU_QTY*(CASE WHEN (ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0))=0 THEN 1 ELSE (ISNULL(Z1.COST_AMT,0)+ISNULL(Z3.COST_AMT,0))/(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0)) END) END),0),2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=C.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN (SELECT A.MO_ID,Z.ITEM_ID,OPENING_WIPEU_QTY,ISSUED_QTY,OPENING_WIPEU_QTY+ISSUED_QTY-ENDING_WIPEU_QTY BQZC,ENDING_WIPEU_QTY FROM COST_MOMAT_WIPEU_D Z LEFT JOIN COST_MOMAT_WIPEU A ON A.COST_MOMAT_WIPEU_ID=Z.COST_MOMAT_WIPEU_ID  WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_CODE='" + comboBox2.Text + "'";
                strSql += " UNION ALL SELECT COST_DOC.MO_ID,COST_ELEMENT.COST_ELEMENT_ID,COST_DOC_D.OPENING_COST_AMT,COST_DOC_D.INPUT_COST_AMT,COST_DOC_D.OPENING_COST_AMT+COST_DOC_D.INPUT_COST_AMT-COST_DOC_D.ENDING_COST_AMT,COST_DOC_D.ENDING_COST_AMT FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC_D.COST_DOC_ID=COST_DOC.COST_DOC_ID LEFT JOIN COST_ELEMENT ON  COST_ELEMENT.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN MO ON MO.MO_ID=COST_DOC.MO_ID  WHERE COST_DOC.ACCOUNT_YEAR='" + comboBox1.Text + "' AND COST_DOC.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND COST_ELEMENT_CATEGORY='1' AND NOT EXISTS(SELECT * FROM COST_MOMAT_WIPEU_D LEFT JOIN COST_MOMAT_WIPEU ON COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID LEFT JOIN ITEM_FINANCIAL ON ITEM_FINANCIAL.ITEM_ID=COST_MOMAT_WIPEU_D.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID WHERE COST_MOMAT_WIPEU_D.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID AND COST_DOC.MO_ID=COST_MOMAT_WIPEU.MO_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID AND COST_MOMAT_WIPEU_D.ENDING_WIPEU_QTY<>0)) C1 ON C1.MO_ID=A.MO_ID ";
                strSql += " LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY*STOCK_ACTION) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT*STOCK_ACTION) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE  EXISTS(SELECT * FROM COST_DOC G1 WHERE G1.MO_ID=A1.MO_ID AND G1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND G1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z1 ON Z1.MO_ID=C1.MO_ID AND C1.ITEM_ID=Z1.ITEM_ID LEFT JOIN ITEM G ON G.ITEM_BUSINESS_ID=C1.ITEM_ID LEFT JOIN COST_ELEMENT L ON L.COST_ELEMENT_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY) FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z2 ON Z2.MO_ID=A.MO_ID  LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z3 ON Z3.MO_ID=A.MO_ID AND Z3.ITEM_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D .REFERENCE_SOURCE_ID_ROid,RECEIPTED_BUSINESS_QTY FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "') Z4 ON Z4.MO_ID=A.MO_ID";
                strSql += " LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO=" + comboBox2.Text + ") GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN FEATURE_GROUP H ON H.FEATURE_GROUP_ID=G.FEATURE_GROUP_ID LEFT JOIN ITEM_FINANCIAL ON C1.ITEM_ID=ITEM_FINANCIAL.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID LEFT JOIN COST_DOC_D ON COST_DOC_D.COST_DOC_ID=A.COST_DOC_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID LEFT JOIN #TEMP_ZTJE K ON K.MO_ID=A.MO_ID AND K.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN COST_ELEMENT ON COST_ELEMENT.COST_ELEMENT_ID=K.COST_ELEMENT_ID LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE C.DOC_NO LIKE '%" + textBox6.Text + "%' AND A.ACCOUNT_YEAR='" + comboBox1.Text + "' AND A.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND DOC.PROCESS_TYPE='1'"; // AND ISNULL(COST_ELEMENT.COST_ELEMENT_CODE,'')<>''
                strSql += " UNION ALL SELECT ISNULL(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,CAST(ROUND(A.COMP_COST_AMT,2) AS REAL) 完工金额,CAST(ROUND(A.ENDING_COST_AMT,2) AS REAL) 未完工金额,(CASE WHEN G.COST_ELEMENT_CATEGORY='2' THEN '人工' WHEN G.COST_ELEMENT_CATEGORY='3' THEN '制造费' WHEN G.COST_ELEMENT_CATEGORY='4' THEN '加工费' ELSE '' END) 费用类型,'' 材料类型,G.COST_ELEMENT_CODE 材料品号,G.COST_ELEMENT_NAME 材料品名,'' 材料规格,0 期初数量,CAST(ROUND(B.OPENING_COST_AMT,2) AS REAL) 期初金额,0 本阶数量,CAST(ROUND(INPUT_CLCOST_AMT,2) AS REAL) 本阶金额,0 下阶数量,CAST(ROUND(INPUT_LLCOST_AMT,2) AS REAL) 下阶金额,0 期末数量,CAST(ROUND(B.ENDING_COST_AMT,2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN COST_DOC_D B ON A.COST_DOC_ID=B.COST_DOC_ID LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=A.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN COST_ELEMENT G ON G.COST_ELEMENT_ID=B.COST_ELEMENT_ID  LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE G.COST_ELEMENT_CATEGORY<>'1' AND C.DOC_NO LIKE '%" + textBox6.Text + "%' AND ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND DOC.PROCESS_TYPE='1'";
            }
            else if (comboBox3.Text == "委外")
            {
                strSql = "SELECT isnull(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,0 完工金额,0 未完工金额,'材料' 费用类型,ISNULL(H.FEATURE_GROUP_NAME,'') 材料类型,ISNULL(G.ITEM_CODE,L.COST_ELEMENT_CODE) 材料品号,ISNULL(G.ITEM_NAME,L.COST_ELEMENT_NAME) 材料品名,ISNULL(G.ITEM_SPECIFICATION,'') 材料规格,CAST(C1.OPENING_WIPEU_QTY AS REAL) 期初数量,CAST(ROUND(ISNULL((CASE WHEN K.QCJE=0 THEN (CASE WHEN K.OPENING_WIPEU_QTY=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.OPENING_COST_AMT/K.OPENING_WIPEU_QTY*C1.OPENING_WIPEU_QTY END) ELSE (COST_DOC_D.OPENING_COST_AMT/K.QCJE)*C1.OPENING_WIPEU_QTY*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 1 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END) END),(CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END)),2) AS REAL) 期初金额,0 本阶数量,0 本阶金额,CAST(ROUND(ISNULL(Z3.ACTUAL_INVENTORY_QTY,0),2) AS REAL) 下阶数量,CAST(ISNULL(Z3.COST_AMT,0) AS REAL) 下阶金额, CAST(C1.ENDING_WIPEU_QTY AS REAL) 期末数量,";
                strSql += " CAST(ROUND(ISNULL((CASE WHEN ISNULL(K.QMJE,0)=0 THEN (CASE WHEN ISNULL(K.ENDING_WIPEU_QTY,0)=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.ENDING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.ENDING_COST_AMT/K.ENDING_WIPEU_QTY*C1.ENDING_WIPEU_QTY END) ELSE (COST_DOC_D.ENDING_COST_AMT/K.QMJE)*C1.ENDING_WIPEU_QTY*(CASE WHEN (ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0))=0 THEN 1 ELSE (ISNULL(Z1.COST_AMT,0)+ISNULL(Z3.COST_AMT,0))/(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0)) END) END),0),2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=C.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN (SELECT A.MO_ID,Z.ITEM_ID,OPENING_WIPEU_QTY,ISSUED_QTY,OPENING_WIPEU_QTY+ISSUED_QTY-ENDING_WIPEU_QTY BQZC,ENDING_WIPEU_QTY FROM COST_MOMAT_WIPEU_D Z LEFT JOIN COST_MOMAT_WIPEU A ON A.COST_MOMAT_WIPEU_ID=Z.COST_MOMAT_WIPEU_ID  WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_CODE='" + comboBox2.Text + "'";
                strSql += " UNION ALL SELECT COST_DOC.MO_ID,COST_ELEMENT.COST_ELEMENT_ID,COST_DOC_D.OPENING_COST_AMT,COST_DOC_D.INPUT_COST_AMT,COST_DOC_D.OPENING_COST_AMT+COST_DOC_D.INPUT_COST_AMT-COST_DOC_D.ENDING_COST_AMT,COST_DOC_D.ENDING_COST_AMT FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC_D.COST_DOC_ID=COST_DOC.COST_DOC_ID LEFT JOIN COST_ELEMENT ON  COST_ELEMENT.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN MO ON MO.MO_ID=COST_DOC.MO_ID WHERE COST_DOC.ACCOUNT_YEAR='" + comboBox1.Text + "' AND COST_DOC.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND COST_ELEMENT_CATEGORY='1' AND NOT EXISTS(SELECT * FROM COST_MOMAT_WIPEU_D LEFT JOIN COST_MOMAT_WIPEU ON COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID LEFT JOIN ITEM_FINANCIAL ON ITEM_FINANCIAL.ITEM_ID=COST_MOMAT_WIPEU_D.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID WHERE COST_MOMAT_WIPEU_D.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID AND COST_DOC.MO_ID=COST_MOMAT_WIPEU.MO_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID AND COST_MOMAT_WIPEU_D.ENDING_WIPEU_QTY<>0)) C1 ON C1.MO_ID=A.MO_ID ";
                strSql += " LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY*STOCK_ACTION) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT*STOCK_ACTION) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE  EXISTS(SELECT * FROM COST_DOC G1 WHERE G1.MO_ID=A1.MO_ID AND G1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND G1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z1 ON Z1.MO_ID=C1.MO_ID AND C1.ITEM_ID=Z1.ITEM_ID LEFT JOIN ITEM G ON G.ITEM_BUSINESS_ID=C1.ITEM_ID LEFT JOIN COST_ELEMENT L ON L.COST_ELEMENT_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY) FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z2 ON Z2.MO_ID=A.MO_ID  LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z3 ON Z3.MO_ID=A.MO_ID AND Z3.ITEM_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D .REFERENCE_SOURCE_ID_ROid,RECEIPTED_BUSINESS_QTY FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "') Z4 ON Z4.MO_ID=A.MO_ID";
                strSql += " LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO=" + comboBox2.Text + ") GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN FEATURE_GROUP H ON H.FEATURE_GROUP_ID=G.FEATURE_GROUP_ID LEFT JOIN ITEM_FINANCIAL ON C1.ITEM_ID=ITEM_FINANCIAL.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID LEFT JOIN COST_DOC_D ON COST_DOC_D.COST_DOC_ID=A.COST_DOC_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID LEFT JOIN #TEMP_ZTJE K ON K.MO_ID=A.MO_ID AND K.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN COST_ELEMENT ON COST_ELEMENT.COST_ELEMENT_ID=K.COST_ELEMENT_ID LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE C.DOC_NO LIKE '%" + textBox6.Text + "%' AND A.ACCOUNT_YEAR='" + comboBox1.Text + "' AND A.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND DOC.PROCESS_TYPE='2'"; // AND ISNULL(COST_ELEMENT.COST_ELEMENT_CODE,'')<>''
                strSql += " UNION ALL SELECT ISNULL(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,CAST(ROUND(A.COMP_COST_AMT,2) AS REAL) 完工金额,CAST(ROUND(A.ENDING_COST_AMT,2) AS REAL) 未完工金额,(CASE WHEN G.COST_ELEMENT_CATEGORY='2' THEN '人工' WHEN G.COST_ELEMENT_CATEGORY='3' THEN '制造费' WHEN G.COST_ELEMENT_CATEGORY='4' THEN '加工费' ELSE '' END) 费用类型,'' 材料类型,G.COST_ELEMENT_CODE 材料品号,G.COST_ELEMENT_NAME 材料品名,'' 材料规格,0 期初数量,CAST(ROUND(B.OPENING_COST_AMT,2) AS REAL) 期初金额,0 本阶数量,CAST(ROUND(INPUT_CLCOST_AMT,2) AS REAL) 本阶金额,0 下阶数量,CAST(ROUND(INPUT_LLCOST_AMT,2) AS REAL) 下阶金额,0 期末数量,CAST(ROUND(B.ENDING_COST_AMT,2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN COST_DOC_D B ON A.COST_DOC_ID=B.COST_DOC_ID LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=A.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN COST_ELEMENT G ON G.COST_ELEMENT_ID=B.COST_ELEMENT_ID  LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE G.COST_ELEMENT_CATEGORY<>'1' AND C.DOC_NO LIKE '%" + textBox6.Text + "%' AND ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND DOC.PROCESS_TYPE='2'";
            }
            else
            {
                strSql = "SELECT isnull(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,0 完工金额,0 未完工金额,'材料' 费用类型,ISNULL(H.FEATURE_GROUP_NAME,'') 材料类型,ISNULL(G.ITEM_CODE,L.COST_ELEMENT_CODE) 材料品号,ISNULL(G.ITEM_NAME,L.COST_ELEMENT_NAME) 材料品名,ISNULL(G.ITEM_SPECIFICATION,'') 材料规格,CAST(C1.OPENING_WIPEU_QTY AS REAL) 期初数量,CAST(ROUND(ISNULL((CASE WHEN K.QCJE=0 THEN (CASE WHEN K.OPENING_WIPEU_QTY=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.OPENING_COST_AMT/K.OPENING_WIPEU_QTY*C1.OPENING_WIPEU_QTY END) ELSE (COST_DOC_D.OPENING_COST_AMT/K.QCJE)*C1.OPENING_WIPEU_QTY*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 1 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END) END),(CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.OPENING_WIPEU_QTY ELSE 0 END)),2) AS REAL) 期初金额,0 本阶数量,0 本阶金额,CAST(ROUND(ISNULL(Z3.ACTUAL_INVENTORY_QTY,0),2) AS REAL) 下阶数量,CAST(ISNULL(Z3.COST_AMT,0) AS REAL) 下阶金额, CAST(C1.ENDING_WIPEU_QTY AS REAL) 期末数量,";
                strSql += " CAST(ROUND(ISNULL((CASE WHEN ISNULL(K.QMJE,0)=0 THEN (CASE WHEN ISNULL(K.ENDING_WIPEU_QTY,0)=0 THEN (CASE WHEN ISNULL(G.ITEM_CODE,'')='' THEN C1.ENDING_WIPEU_QTY ELSE 0 END) ELSE COST_DOC_D.ENDING_COST_AMT/K.ENDING_WIPEU_QTY*C1.ENDING_WIPEU_QTY END) ELSE (COST_DOC_D.ENDING_COST_AMT/K.QMJE)*C1.ENDING_WIPEU_QTY*(CASE WHEN (ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0))=0 THEN 1 ELSE (ISNULL(Z1.COST_AMT,0)+ISNULL(Z3.COST_AMT,0))/(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0)) END) END),0),2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=C.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN (SELECT A.MO_ID,Z.ITEM_ID,OPENING_WIPEU_QTY,ISSUED_QTY,OPENING_WIPEU_QTY+ISSUED_QTY-ENDING_WIPEU_QTY BQZC,ENDING_WIPEU_QTY FROM COST_MOMAT_WIPEU_D Z LEFT JOIN COST_MOMAT_WIPEU A ON A.COST_MOMAT_WIPEU_ID=Z.COST_MOMAT_WIPEU_ID  WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_CODE='" + comboBox2.Text + "'";
                strSql += " UNION ALL SELECT COST_DOC.MO_ID,COST_ELEMENT.COST_ELEMENT_ID,COST_DOC_D.OPENING_COST_AMT,COST_DOC_D.INPUT_COST_AMT,COST_DOC_D.OPENING_COST_AMT+COST_DOC_D.INPUT_COST_AMT-COST_DOC_D.ENDING_COST_AMT,COST_DOC_D.ENDING_COST_AMT FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC_D.COST_DOC_ID=COST_DOC.COST_DOC_ID LEFT JOIN COST_ELEMENT ON  COST_ELEMENT.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN MO ON MO.MO_ID=COST_DOC.MO_ID WHERE COST_DOC.ACCOUNT_YEAR='" + comboBox1.Text + "' AND COST_DOC.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND COST_ELEMENT_CATEGORY='1' AND NOT EXISTS(SELECT * FROM COST_MOMAT_WIPEU_D LEFT JOIN COST_MOMAT_WIPEU ON COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID LEFT JOIN ITEM_FINANCIAL ON ITEM_FINANCIAL.ITEM_ID=COST_MOMAT_WIPEU_D.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID WHERE COST_MOMAT_WIPEU_D.COST_MOMAT_WIPEU_ID=COST_MOMAT_WIPEU.COST_MOMAT_WIPEU_ID AND COST_DOC.MO_ID=COST_MOMAT_WIPEU.MO_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID AND COST_MOMAT_WIPEU_D.ENDING_WIPEU_QTY<>0)) C1 ON C1.MO_ID=A.MO_ID ";
                strSql += " LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY*STOCK_ACTION) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT*STOCK_ACTION) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE  EXISTS(SELECT * FROM COST_DOC G1 WHERE G1.MO_ID=A1.MO_ID AND G1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND G1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z1 ON Z1.MO_ID=C1.MO_ID AND C1.ITEM_ID=Z1.ITEM_ID LEFT JOIN ITEM G ON G.ITEM_BUSINESS_ID=C1.ITEM_ID LEFT JOIN COST_ELEMENT L ON L.COST_ELEMENT_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY) FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z2 ON Z2.MO_ID=A.MO_ID  LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z3 ON Z3.MO_ID=A.MO_ID AND Z3.ITEM_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D .REFERENCE_SOURCE_ID_ROid,RECEIPTED_BUSINESS_QTY FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "') Z4 ON Z4.MO_ID=A.MO_ID";
                strSql += " LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO=" + comboBox2.Text + ") GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN FEATURE_GROUP H ON H.FEATURE_GROUP_ID=G.FEATURE_GROUP_ID LEFT JOIN ITEM_FINANCIAL ON C1.ITEM_ID=ITEM_FINANCIAL.ITEM_ID LEFT JOIN IM_COST_CATEGORY ON IM_COST_CATEGORY.IM_COST_CATEGORY_ID=ITEM_FINANCIAL.IM_COST_CATEGORY_ID LEFT JOIN COST_DOC_D ON COST_DOC_D.COST_DOC_ID=A.COST_DOC_ID AND COST_DOC_D.COST_ELEMENT_ID=IM_COST_CATEGORY.PURPRICE_COST_ELEMENT_ID LEFT JOIN #TEMP_ZTJE K ON K.MO_ID=A.MO_ID AND K.COST_ELEMENT_ID=COST_DOC_D.COST_ELEMENT_ID LEFT JOIN COST_ELEMENT ON COST_ELEMENT.COST_ELEMENT_ID=K.COST_ELEMENT_ID  LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE C.DOC_NO LIKE '%" + textBox6.Text + "%' AND A.ACCOUNT_YEAR='" + comboBox1.Text + "' AND A.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "'"; // AND ISNULL(COST_ELEMENT.COST_ELEMENT_CODE,'')<>''
                strSql += " UNION ALL SELECT ISNULL(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,CAST(ROUND(A.COMP_COST_AMT,2) AS REAL) 完工金额,CAST(ROUND(A.ENDING_COST_AMT,2) AS REAL) 未完工金额,(CASE WHEN G.COST_ELEMENT_CATEGORY='2' THEN '人工' WHEN G.COST_ELEMENT_CATEGORY='3' THEN '制造费' WHEN G.COST_ELEMENT_CATEGORY='4' THEN '加工费' ELSE '' END) 费用类型,'' 材料类型,G.COST_ELEMENT_CODE 材料品号,G.COST_ELEMENT_NAME 材料品名,'' 材料规格,0 期初数量,CAST(ROUND(B.OPENING_COST_AMT,2) AS REAL) 期初金额,0 本阶数量,CAST(ROUND(INPUT_CLCOST_AMT,2) AS REAL) 本阶金额,0 下阶数量,CAST(ROUND(INPUT_LLCOST_AMT,2) AS REAL) 下阶金额,0 期末数量,CAST(ROUND(B.ENDING_COST_AMT,2) AS REAL) 期末金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN COST_DOC_D B ON A.COST_DOC_ID=B.COST_DOC_ID LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=A.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN COST_ELEMENT G ON G.COST_ELEMENT_ID=B.COST_ELEMENT_ID  LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID  WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN DOC ON C.DOC_ID=DOC.DOC_ID WHERE G.COST_ELEMENT_CATEGORY<>'1' AND C.DOC_NO LIKE '%" + textBox6.Text + "%' AND ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "'";
            }
            //textBox6.Text = strSql;
            
            try
            {
                DataTable dt;
                gridControl5.DataSource = null;
                gridControl5.Refresh();
                dt = db.GetDataSet2(strSql0, strSql, "PURTD").Tables["PURTD"];
                gridControl5.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }

            //MessageBox.Show(bbny,"系统提示");
            //return;
            //strSql = "SELECT isnull(E.UDF023,'') 产品类型,ISNULL(E.UDF024,'') 产品型号,E.ITEM_CODE 产品编码,E.ITEM_NAME 产品名称,E.ITEM_SPECIFICATION 产品规格,F.UNIT_NAME 单位, C.UDF021 订单单号, C.DOC_NO 工单单号,CAST((A.PROD_COMPLETED_QTY+A.SUB_COMPLETED_QTY) AS REAL) 完工数量,0 完工金额,0 未完工金额,'材料' 费用类型,H.FEATURE_GROUP_NAME 材料类型,G.ITEM_CODE 材料品号,G.ITEM_NAME 材料品名,G.ITEM_SPECIFICATION 材料规格,CAST(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)-ISNULL((CASE WHEN C.PLAN_QTY=0 THEN Z2.REQUIRED_QTY*C1.REQUIRED_QTY ELSE Z2.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY END),0) AS REAL) 期初数量,CAST(ROUND((ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)-ISNULL((CASE WHEN C.PLAN_QTY=0 THEN Z2.REQUIRED_QTY*C1.REQUIRED_QTY ELSE Z2.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY END),0))*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 0 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END),2) AS REAL) 期初金额,0 本阶数量,0 本阶金额,CAST(ROUND(ISNULL(Z3.ACTUAL_INVENTORY_QTY,0),2) AS REAL) 下阶数量,CAST(ISNULL(Z3.COST_AMT,0) AS REAL) 下阶金额,(CASE WHEN (C.[STATUS]='Y' OR C.[STATUS]='y') AND YEAR(ACTUAL_COMPLETE_DATE)='" + comboBox1.Text + "' AND MONTH(ACTUAL_COMPLETE_DATE)='" + comboBox2.Text + "' THEN CAST(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)-ISNULL((CASE WHEN C.PLAN_QTY=0 THEN Z2.REQUIRED_QTY*C1.REQUIRED_QTY ELSE Z2.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY END),0) AS REAL)+CAST(ROUND(ISNULL(Z3.ACTUAL_INVENTORY_QTY,0),2) AS REAL) ELSE CAST(ROUND((CASE WHEN ISNULL(C.PLAN_QTY,0)=0 THEN ISNULL(Z4.REQUIRED_QTY,0) ELSE ISNULL(Z4.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY,0) END),2) AS REAL) END) 完成数量,";
            //strSql += " (CASE WHEN (C.[STATUS]='Y' OR C.[STATUS]='y') AND YEAR(ACTUAL_COMPLETE_DATE)='" + comboBox1.Text + "' AND MONTH(ACTUAL_COMPLETE_DATE)='" + comboBox2.Text + "' THEN CAST(ROUND((ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)-ISNULL((CASE WHEN C.PLAN_QTY=0 THEN Z2.REQUIRED_QTY*C1.REQUIRED_QTY ELSE Z2.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY END),0))*(CASE WHEN ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)=0 THEN 0 ELSE ISNULL(Z1.COST_AMT,0)/ISNULL(Z1.ACTUAL_INVENTORY_QTY,0) END),2) AS REAL)+CAST(ISNULL(Z3.COST_AMT,0) AS REAL) ELSE CAST(ROUND((CASE WHEN ISNULL(C.PLAN_QTY,0)=0 THEN ISNULL(Z4.REQUIRED_QTY,0) ELSE ISNULL(Z4.REQUIRED_QTY*C1.REQUIRED_QTY/C.PLAN_QTY,0) END),2) AS REAL)*CAST(ROUND((CASE WHEN (ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0))=0 THEN 0 ELSE (ISNULL(Z1.COST_AMT,0)+ISNULL(Z3.COST_AMT,0))/(ISNULL(Z1.ACTUAL_INVENTORY_QTY,0)+ISNULL(Z3.ACTUAL_INVENTORY_QTY,0)) END),2) AS REAL) END) 完成金额,0 FLAG FROM (SELECT * FROM COST_DOC WHERE ACCOUNT_YEAR='" + comboBox1.Text + "' AND ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "' AND (OPENING_COST_AMT+INPUT_COST_AMT+COMP_COST_AMT)<>0) A LEFT JOIN MO C ON C.MO_ID=A.MO_ID LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=C.ITEM_ID LEFT JOIN UNIT F ON F.UNIT_ID=E.STOCK_UNIT_ID LEFT JOIN (SELECT MO_ID,ITEM_ID,SUM(REQUIRED_QTY) REQUIRED_QTY FROM MO_D WHERE EXISTS(SELECT * FROM COST_DOC D1 WHERE D1.MO_ID=MO_D.MO_ID AND D1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND D1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_ID,ITEM_ID) C1 ON C1.MO_ID=A.MO_ID ";
            //strSql += " LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE  EXISTS(SELECT * FROM COST_DOC G1 WHERE G1.MO_ID=A1.MO_ID AND G1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND G1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z1 ON Z1.MO_ID=C1.MO_ID AND C1.ITEM_ID=Z1.ITEM_ID LEFT JOIN ITEM G ON G.ITEM_BUSINESS_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY) FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)<'" + bbny + "' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z2 ON Z2.MO_ID=A.MO_ID  LEFT JOIN (SELECT A1.MO_ID,A1.ITEM_ID,SUM(A1.ACTUAL_INVENTORY_QTY) ACTUAL_INVENTORY_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM ISSUE_RECEIPT_D A1 LEFT JOIN MO B1 ON A1.MO_ID=B1.MO_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=A1.ISSUE_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' GROUP BY A1.MO_ID,A1.ITEM_ID) Z3 ON Z3.MO_ID=A.MO_ID AND Z3.ITEM_ID=C1.ITEM_ID LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY AS REQUIRED_QTY FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "' UNION ALL SELECT PURCHASE_ARRIVAL_D .REFERENCE_SOURCE_ID_ROid,RECEIPTED_BUSINESS_QTY FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND CAST(ACCOUNT_YEAR AS CHAR(4))+RIGHT('0'+RTRIM(CAST(ACCOUNT_PERIOD_SEQNO AS CHAR(2))),2)='" + bbny + "') Z4 ON Z4.MO_ID=A.MO_ID";
            //strSql += " LEFT JOIN (SELECT MO_RECEIPT_D.MO_ID,SUM(SCRAP_QTY+ACCEPTED_QTY+DESTROYED_QTY) AS REQUIRED_QTY,SUM(TRANSACTION_LINE.COST_AMT) COST_AMT FROM MO_RECEIPT_D LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=MO_RECEIPT_D_ID WHERE EXISTS(SELECT * FROM COST_DOC E1 WHERE E1.MO_ID=MO_RECEIPT_D.MO_ID AND E1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND E1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY MO_RECEIPT_D.MO_ID UNION ALL SELECT PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid,SUM(RECEIPTED_BUSINESS_QTY),SUM(TRANSACTION_LINE.COST_AMT)  FROM PURCHASE_RECEIPT_D LEFT JOIN PURCHASE_ARRIVAL_D ON PURCHASE_RECEIPT_D.SOURCE_ID=PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID LEFT JOIN TRANSACTION_LINE ON TRANSACTION_LINE.SOURCE_ID_ROid=PURCHASE_RECEIPT_D_ID WHERE PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_RTK='MO' AND EXISTS(SELECT * FROM COST_DOC F1 WHERE F1.MO_ID=PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid AND F1.ACCOUNT_YEAR='" + comboBox1.Text + "' AND F1.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "') GROUP BY PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID_ROid) Z5 ON Z5.MO_ID=A.MO_ID LEFT JOIN FEATURE_GROUP H ON H.FEATURE_GROUP_ID=G.FEATURE_GROUP_ID WHERE C.DOC_NO LIKE '%" + textBox6.Text + "%' AND A.ACCOUNT_YEAR='" + comboBox1.Text + "' AND A.ACCOUNT_PERIOD_SEQNO='" + comboBox2.Text + "'";




            //汇总完工金额和未完工金额
            progressBar3.Visible = true;
            progressBar3.Maximum = bandedGridView2.RowCount;//设置最大长度值
            progressBar3.Value = 0;//设置当前值
            progressBar3.Step = 1;//设置没次增长多少
            for (int i = 0; i <= bandedGridView2.RowCount - 1; i++)
            {
                try
                {
                    if (Convert.ToDouble(bandedGridView2.GetRowCellValue(i, "完工金额").ToString()) < 0.1 && Convert.ToDouble(bandedGridView2.GetRowCellValue(i, "完工金额").ToString()) > -0.01)
                    {
                        bandedGridView2.SetRowCellValue(i, "完工金额", "0.00");
                    }
                    if (Convert.ToDouble(bandedGridView2.GetRowCellValue(i, "完工金额").ToString()) < 0.1 && Convert.ToDouble(bandedGridView2.GetRowCellValue(i, "完工金额").ToString()) > -0.01)
                    {
                        bandedGridView2.SetRowCellValue(i, "完工金额", "0.00");
                    }
                }
                catch
                {

                }
                //MessageBox.Show("1","系统提示");
                String jsflg = bandedGridView2.GetRowCellValue(i, "FLAG").ToString();
                String gdha = bandedGridView2.GetRowCellValue(i, "工单单号").ToString();
                //MessageBox.Show(jsflg, "系统提示2");
                if (jsflg == "0")
                {

                    // MessageBox.Show("3", "系统提示3");
                    double wgje = 0.00;
                    double wwgje = 0.00;
                    for (int j = 0; j <= bandedGridView2.RowCount - 1; j++)
                    {
                        String gdhb = bandedGridView2.GetRowCellValue(j, "工单单号").ToString();
                        if (gdha == gdhb)
                        {
                            wgje += Convert.ToDouble(bandedGridView2.GetRowCellValue(j, "完工金额").ToString());
                            wwgje += Convert.ToDouble(bandedGridView2.GetRowCellValue(j, "期末金额").ToString());
                        }
                    }
                    if (wgje <= 0.1 && wgje > -0.1)
                    {
                        wgje = 0.0;
                    }
                    if (wwgje <= 0.1 && wwgje > -0.1)
                    {
                        wwgje = 0.0;
                    }
                    for (int k = i; k <= bandedGridView2.RowCount - 1; k++)
                    {
                        String jsflgc = bandedGridView2.GetRowCellValue(k, "FLAG").ToString();
                        String gdhc = bandedGridView2.GetRowCellValue(k, "工单单号").ToString();
                        try
                        {
                            if (jsflgc == "0" && gdha == gdhc)
                            {
                                bandedGridView2.SetRowCellValue(k, "完工金额", wgje.ToString());
                                bandedGridView2.SetRowCellValue(k, "未完工金额", wwgje.ToString());
                                bandedGridView2.SetRowCellValue(k, "FLAG", "1");
                            }
                        }
                        catch
                        {

                        }
                    }
                }
                progressBar3.Value += progressBar3.Step;
            }
            progressBar3.Visible = false;
        }

        private void gridView4_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            // string strSql;
            // String zjph = gridView4.GetFocusedRowCellValue("品号").ToString();
            // //String gcph = bandedGridView1.GetFocusedRowCellValue("工厂ID").ToString();
            // strSql = "SELECT B.ITEM_CODE 材料品号,B.ITEM_NAME 材料品名,B.ITEM_SPECIFICATION 材料规格,CAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL+CE3_CUR_LEVEL+CE4_CUR_LEVEL+CE5_CUR_LEVEL+CE6_CUR_LEVEL+CE7_CUR_LEVEL+CE8_CUR_LEVEL+CE9_CUR_LEVEL+CE10_CUR_LEVEL+CE11_CUR_LEVEL+CE12_CUR_LEVEL+CE13_CUR_LEVEL+CE14_CUR_LEVEL+CE15_CUR_LEVEL+CE16_CUR_LEVEL+CE17_CUR_LEVEL+CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((CE5_CUR_LEVEL + CE6_CUR_LEVEL + CE7_CUR_LEVEL + CE8_CUR_LEVEL + CE9_CUR_LEVEL + CE10_CUR_LEVEL + CE11_CUR_LEVEL + CE13_CUR_LEVEL + CE14_CUR_LEVEL + CE15_CUR_LEVEL + CE16_CUR_LEVEL + CE17_CUR_LEVEL + CE18_CUR_LEVEL) as real) 本阶材料,cast(CE1_CUR_LEVEL as real) 本阶人工,cast(CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL as real) 本阶制费,CAST(CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=Z.PARENT_ITEM_ID WHERE LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))=1 AND C.ITEM_CODE LIKE '" + zjph + "%' AND VERSION_TIMES='01' ORDER BY Z.BOM_STRUCTURE_CODE";

            //// textBox1.Text = strSql;
            // try
            // {
            //     DataTable dataTable;
            //     dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
            //     gridControl6.DataSource = dataTable;
            // }
            // catch
            // {

            // }
        }

        private void gridView4_RowCellClick(object sender, DevExpress.XtraGrid.Views.Grid.RowCellClickEventArgs e)
        {
            if (e.Column.ColumnHandle == gridView4.Columns["A型号"].ColumnHandle || e.Column.ColumnHandle == gridView4.Columns["B型号"].ColumnHandle)
            {
                String zjph = gridView4.FocusedColumn.Tag.ToString();
                String yjph = gridView4.GetFocusedRowCellValue("类型").ToString();
                String strSql = "SELECT B.ITEM_CODE 品号 FROM SIMULATE_COST_RESULT K LEFT JOIN ITEM A ON K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON K.ITEM_ID=B.ITEM_BUSINESS_ID  WHERE A.ITEM_CODE='" + zjph + "' AND RTRIM(B.UDF023)='" + yjph + "'  AND VERSION_TIMES='01'";
                String mxph = "";
                try
                {
                    sdr1 = db.GetDataReader1(strSql);
                    while (sdr1.Read())
                    {
                        mxph = sdr1["品号"].ToString();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    throw ex;
                }
                finally
                {
                    sdr1.Close();
                }

                strSql = "SELECT B.ITEM_CODE 材料品号,B.ITEM_NAME 材料品名,B.ITEM_SPECIFICATION 材料规格,CAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL+CE3_CUR_LEVEL+CE4_CUR_LEVEL+CE5_CUR_LEVEL+CE6_CUR_LEVEL+CE7_CUR_LEVEL+CE8_CUR_LEVEL+CE9_CUR_LEVEL+CE10_CUR_LEVEL+CE11_CUR_LEVEL+CE12_CUR_LEVEL+CE13_CUR_LEVEL+CE14_CUR_LEVEL+CE15_CUR_LEVEL+CE16_CUR_LEVEL+CE17_CUR_LEVEL+CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((CE5_CUR_LEVEL + CE6_CUR_LEVEL + CE7_CUR_LEVEL + CE8_CUR_LEVEL + CE9_CUR_LEVEL + CE10_CUR_LEVEL + CE11_CUR_LEVEL + CE13_CUR_LEVEL + CE14_CUR_LEVEL + CE15_CUR_LEVEL + CE16_CUR_LEVEL + CE17_CUR_LEVEL + CE18_CUR_LEVEL+CE5_LOW_LEVEL + CE6_LOW_LEVEL + CE7_LOW_LEVEL + CE8_LOW_LEVEL + CE9_LOW_LEVEL + CE10_LOW_LEVEL + CE11_LOW_LEVEL + CE13_LOW_LEVEL + CE14_LOW_LEVEL + CE15_LOW_LEVEL + CE16_LOW_LEVEL + CE17_LOW_LEVEL + CE18_LOW_LEVEL) as real) 材料,cast(CE1_CUR_LEVEL+CE1_LOW_LEVEL as real) 人工,cast(CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL+CE2_LOW_LEVEL + CE3_LOW_LEVEL + CE4_LOW_LEVEL as real) 制费,CAST(CE12_CUR_LEVEL+CE12_LOW_LEVEL AS REAL) 加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=Z.PARENT_ITEM_ID WHERE LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))=1 AND C.ITEM_CODE='" + mxph + "' AND VERSION_TIMES='01' ";
                strSql += " UNION ALL SELECT B.ITEM_CODE 材料品号,B.ITEM_NAME 材料品名,B.ITEM_SPECIFICATION 材料规格,CAST((CE1_CUR_LEVEL+CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL+CE12_CUR_LEVEL) AS REAL) 单位成本,cast((CE5_CUR_LEVEL + CE6_CUR_LEVEL + CE7_CUR_LEVEL + CE8_CUR_LEVEL + CE9_CUR_LEVEL + CE10_CUR_LEVEL + CE11_CUR_LEVEL + CE13_CUR_LEVEL + CE14_CUR_LEVEL + CE15_CUR_LEVEL + CE16_CUR_LEVEL + CE17_CUR_LEVEL + CE18_CUR_LEVEL) as real) 材料,cast(CE1_CUR_LEVEL as real) 人工,cast(CE2_CUR_LEVEL + CE3_CUR_LEVEL + CE4_CUR_LEVEL as real) 制费,CAST(CE12_CUR_LEVEL AS REAL) 加工费 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM B ON Z.ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID=Z.PARENT_ITEM_ID WHERE B.ITEM_CODE='" + mxph + "' AND C.ITEM_CODE='" + mxph + "' AND VERSION_TIMES='01' ";
                textBox8.Text = strSql;
                try
                {
                    DataTable dataTable;
                    dataTable = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                    if (e.Column.ColumnHandle == gridView4.Columns["A型号"].ColumnHandle)
                    {
                        gridControl7.DataSource = dataTable;
                    }
                    else
                    {
                        gridControl6.DataSource = dataTable;
                    }

                }
                catch
                {

                }



            }
        }

        private void CBFXPT_Load(object sender, EventArgs e)
        {

        }

        private void toolTipController1_GetActiveObjectInfo(object sender, DevExpress.Utils.ToolTipControllerGetActiveObjectInfoEventArgs e)
        {
            if (e.SelectedControl != gridControl4) return;

            ToolTipControlInfo info = null;
            //Get the view at the current mouse position
            GridView view = gridControl4.GetViewAt(e.ControlMousePosition) as GridView;
            if (view == null) return;
            //Get the view's element information that resides at the current position
            GridHitInfo hi = view.CalcHitInfo(e.ControlMousePosition);
            //Display a hint for row indicator cells
            if (hi.HitTest == GridHitTest.RowIndicator)
            {
                //An object that uniquely identifies a row indicator cell
                object o = hi.HitTest.ToString() + hi.RowHandle.ToString();
                string text = "Row " + hi.RowHandle.ToString();
                info = new ToolTipControlInfo(o, text);
            }
            //Supply tooltip information if applicable, otherwise preserve default tooltip (if any)
            if (info != null)
                e.Info = info;
        }

        private void 导出EXCELToolStripMenuItem_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl7.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void 导出EXCELToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl6.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void 复制ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string rowCellmsg = string.Empty;
            GridViewInfo info = gridView7.GetViewInfo() as GridViewInfo;
            GridCellInfo cellInfo = info.GetGridCellInfo(gridView7.FocusedRowHandle, gridView7.FocusedColumn);

            if (cellInfo == null) return;

            if (cellInfo.CellValue == null) Clipboard.Clear();　　//可能会产生null抛出异常,故需要清空剪贴板信息
            else
                rowCellmsg = cellInfo.CellValue.ToString();

            if (string.IsNullOrEmpty(rowCellmsg))　　　　　　　　　　//单元格内容为空的时候,不能复制为空消息,故需要清空剪贴板的信息
            {
                Clipboard.Clear();
            }
            else
            {
                Clipboard.SetText(rowCellmsg);
            }
        }

        private void 复制ToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            string rowCellmsg = string.Empty;
            GridViewInfo info = gridView6.GetViewInfo() as GridViewInfo;
            GridCellInfo cellInfo = info.GetGridCellInfo(gridView6.FocusedRowHandle, gridView6.FocusedColumn);
            if (cellInfo == null) return;
            if (cellInfo.CellValue == null) Clipboard.Clear();　　//可能会产生null抛出异常,故需要清空剪贴板信息
            else
                rowCellmsg = cellInfo.CellValue.ToString();
            if (string.IsNullOrEmpty(rowCellmsg))　　　　　　　　　　//单元格内容为空的时候,不能复制为空消息,故需要清空剪贴板的信息
            {
                Clipboard.Clear();
            }
            else
            {
                Clipboard.SetText(rowCellmsg);
            }
        }

        private void button11_Click(object sender, EventArgs e)
        {
            String strSql = "";

            if (radioButton1.Checked == true)
            {
                strSql = "SELECT ITEM_CODE 品号,ITEM_NAME 品名,ITEM_SPECIFICATION 规格 FROM ITEM WHERE ITEM.ApproveStatus='Y' AND (EXISTS(SELECT * FROM BOM_D WHERE SOURCE_ID_ROid=ITEM.ITEM_BUSINESS_ID) OR EXISTS(SELECT * FROM BOM_D WHERE BOM_D.PARENT_ITEM_ID=ITEM.ITEM_BUSINESS_ID)) AND NOT EXISTS(SELECT * FROM ITEM_FINANCIAL WHERE ITEM_FINANCIAL.ITEM_ID=ITEM.ITEM_BUSINESS_ID)";
            }
            else if (radioButton2.Checked == true)
            {
                strSql = "SELECT ITEM_CODE 品号,ITEM_NAME 品名,ITEM_SPECIFICATION 规格 FROM ITEM LEFT JOIN ITEM_PLANT ON ITEM.ITEM_BUSINESS_ID=ITEM_PLANT.ITEM_ID WHERE ITEM_PLANT.ITEM_PROPERTY<>'P' AND ITEM.ApproveStatus='Y' AND (EXISTS(SELECT * FROM BOM_D WHERE SOURCE_ID_ROid=ITEM.ITEM_BUSINESS_ID) OR EXISTS(SELECT * FROM BOM_D WHERE BOM_D.PARENT_ITEM_ID=ITEM.ITEM_BUSINESS_ID)) AND NOT EXISTS(SELECT * FROM SIMULATE_COST_RESOURCE WHERE SIMULATE_COST_RESOURCE.ITEM_ID=ITEM.ITEM_BUSINESS_ID)";
            }
            else
            {
                strSql = "SELECT ITEM_CODE 品号,ITEM_NAME 品名,ITEM_SPECIFICATION 规格,CAST(CE1_CUR_LEVEL AS REAL) 人工,CAST(CE3_CUR_LEVEL AS REAL) 制费 FROM SIMULATE_COST_RESOURCE LEFT JOIN ITEM ON SIMULATE_COST_RESOURCE.ITEM_ID=ITEM.ITEM_BUSINESS_ID WHERE (CE1_CUR_LEVEL=0 OR CE3_CUR_LEVEL=0) AND ITEM.ITEM_CODE NOT LIKE '9%'";
            }
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl8.DataSource = dt;
            }
            catch
            {

            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl8.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            String strSql = "";
            strSql = "UPDATE SIMULATE_COST_RESOURCE SET CE1_CUR_LEVEL=B.PIECE_PRICE FROM (SELECT ITEM_ID,SUM(PIECE_PRICE) PIECE_PRICE FROM ITEM_ROUTING_D LEFT JOIN ITEM_ROUTING ON ITEM_ROUTING.ITEM_ROUTING_ID=ITEM_ROUTING_D.ITEM_ROUTING_ID  WHERE ITEM_ROUTING.ROUTING_CODE='1' GROUP BY ITEM_ID) B WHERE B.ITEM_ID=SIMULATE_COST_RESOURCE.ITEM_ID";
            try
            {

                if (db.ExecDataBySql1(strSql) > 0)
                {
                    MessageBox.Show("更新成功，请核查！", "软件提示");
                }
                else
                {
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
        }

        private void button14_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 3;
            DataTable dt = new DataTable();
            String pm = this.bandedGridView1.GetFocusedRowCellValue("品名").ToString();
            String ph = this.bandedGridView1.GetFocusedRowCellValue("品号").ToString();
            String strSql = "SELECT DISTINCT LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', '')) 阶次,RTRIM(A.ITEM_CODE) 品号,RTRIM(A.ITEM_NAME) 品名,RTRIM(A.ITEM_SPECIFICATION) 规格,ITEM_PLANT.ITEM_PROPERTY 品号属性,'' 标准,'' 实际,0.00 差异 FROM SIMULATE_COST_RESULT Z LEFT JOIN ITEM A ON Z.ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON Z.PARENT_ITEM_ID=B.ITEM_BUSINESS_ID LEFT JOIN ITEM_PLANT ON Z.Owner_Org_ROid=ITEM_PLANT.Owner_Org_ROid AND Z.ITEM_ID=ITEM_PLANT.ITEM_ID WHERE  LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', ''))<=2 AND RTRIM(B.ITEM_CODE)='" + ph + "' AND Z.VERSION_TIMES='01'  ORDER BY LEN(Z.BOM_STRUCTURE_CODE) - LEN(REPLACE(Z.BOM_STRUCTURE_CODE, '-', '')),RTRIM(A.ITEM_CODE)"; //AND RTRIM(A.UDF023)<> ''
            //textBox9.Text = strSql;
            //return;
            try
            {
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl9.DataSource = dt;
            }
            catch
            {

            }


                progressBar4.Visible = true;
                progressBar4.Value = 0;//设置当前值
                progressBar4.Step = 1;//设置没次增长多少
                progressBar4.Maximum = gridView9.RowCount;
            for (int i = 0; i <= gridView9.RowCount - 1; i++)
            {
                progressBar4.Value += progressBar4.Step;
                String zjph = this.bandedGridView1.GetFocusedRowCellValue("品号").ToString();
                //MessageBox.Show(zjph, "123");
                String yjph = gridView9.GetRowCellValue(i, "品号").ToString();
                String phsx = gridView9.GetRowCellValue(i, "品号属性").ToString();
               
                   strSql = "SELECT B.ITEM_NAME 品名,B.ITEM_SPECIFICATION 规格,CAST((K.CE1_CUR_LEVEL+K.CE2_CUR_LEVEL+K.CE3_CUR_LEVEL+K.CE4_CUR_LEVEL+K.CE5_CUR_LEVEL+K.CE6_CUR_LEVEL+K.CE7_CUR_LEVEL+K.CE8_CUR_LEVEL+K.CE9_CUR_LEVEL+K.CE10_CUR_LEVEL+K.CE11_CUR_LEVEL+K.CE12_CUR_LEVEL+K.CE13_CUR_LEVEL+K.CE14_CUR_LEVEL+K.CE15_CUR_LEVEL+K.CE16_CUR_LEVEL+K.CE17_CUR_LEVEL+K.CE18_CUR_LEVEL+CE1_LOW_LEVEL+CE2_LOW_LEVEL+CE3_LOW_LEVEL+CE4_LOW_LEVEL+CE5_LOW_LEVEL+CE6_LOW_LEVEL+CE7_LOW_LEVEL+CE8_LOW_LEVEL+CE9_LOW_LEVEL+CE10_LOW_LEVEL+CE11_LOW_LEVEL+CE12_LOW_LEVEL+CE13_LOW_LEVEL+CE14_LOW_LEVEL+CE15_LOW_LEVEL+CE16_LOW_LEVEL+CE17_LOW_LEVEL+CE18_LOW_LEVEL) as real) 单位成本,cast((K.CE5_CUR_LEVEL + K.CE6_CUR_LEVEL + K.CE7_CUR_LEVEL + K.CE8_CUR_LEVEL + K.CE9_CUR_LEVEL + K.CE10_CUR_LEVEL + K.CE11_CUR_LEVEL + K.CE13_CUR_LEVEL + K.CE14_CUR_LEVEL + K.CE15_CUR_LEVEL + K.CE16_CUR_LEVEL + K.CE17_CUR_LEVEL + K.CE18_CUR_LEVEL) as real) 本阶材料,cast(K.CE1_CUR_LEVEL as real) 本阶人工,cast(K.CE2_CUR_LEVEL + K.CE3_CUR_LEVEL + K.CE4_CUR_LEVEL as real) 本阶制费,CAST(K.CE12_CUR_LEVEL AS REAL) 本阶加工费 FROM SIMULATE_COST_RESULT K LEFT JOIN ITEM A ON K.PARENT_ITEM_ID=A.ITEM_BUSINESS_ID LEFT JOIN ITEM B ON K.ITEM_ID=B.ITEM_BUSINESS_ID  WHERE A.ITEM_CODE='" + zjph + "' AND RTRIM(B.ITEM_CODE)='" + yjph + "'  AND VERSION_TIMES='01'";

                try
                {
                    sdr1 = db.GetDataReader1(strSql);
                    while (sdr1.Read())
                    {
                        //Double bjtr = Convert.ToDouble(sdr1["本阶材料"].ToString()) + Convert.ToDouble(sdr1["本阶人工"].ToString()) + Convert.ToDouble(sdr1["本阶制费"].ToString()) + Convert.ToDouble(sdr1["本阶加工费"].ToString());
                        Double dwcb = Convert.ToDouble(sdr1["单位成本"].ToString());
                        gridView9.SetRowCellValue(i, "标准", dwcb.ToString("0.##"));
                        //gridView9.SetRowCellValue(i, "品名", sdr1["品名"].ToString());
                        //gridView9.SetRowCellValue(i, "规格", sdr1["规格"].ToString());
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    throw ex;
                }
                finally
                {
                    sdr1.Close();
                }
            //抓实际成本
            if (phsx == "Y")
            {
                   strSql = "SELECT SUM(ISNULL(Z.单位成本,0)) 单位成本 FROM BOM_D LEFT JOIN (SELECT Z.ITEM_ID,(SELECT ISNULL(SUM(A.COST_AMT),0)/ Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID) 单位成本 FROM TRANSACTION_LINE Z WHERE CATEGORY = '14' AND STOCK_ACTION = -1 AND INVENTORY_QTY> 0 AND NOT EXISTS(SELECT * FROM TRANSACTION_LINE Z1 WHERE Z1.ITEM_ID = Z.ITEM_ID AND Z1.CATEGORY = '14' AND Z1.STOCK_ACTION = -1 AND Z1.INVENTORY_QTY > 0 AND Z.TRANSACTION_DATE < Z1.TRANSACTION_DATE)) Z ON Z.ITEM_ID=BOM_D.SOURCE_ID_ROid LEFT JOIN ITEM ON ITEM.ITEM_BUSINESS_ID=BOM_D.PARENT_ITEM_ID WHERE ITEM.ITEM_CODE='" + yjph + "'";
            }
            else
            {
                   strSql = "SELECT ISNULL((SELECT ISNULL(SUM(A.COST_AMT),0)/ Z.INVENTORY_QTY FROM TRANS_LINE_COST_DETAIL A WHERE A.TRANSACTION_LINE_ID = Z.TRANSACTION_LINE_ID),0) 单位成本 FROM TRANSACTION_LINE Z LEFT JOIN ITEM ON Z.ITEM_ID=ITEM.ITEM_BUSINESS_ID WHERE ITEM_CODE='" + yjph + "' AND CATEGORY = '14' AND STOCK_ACTION = -1 AND INVENTORY_QTY> 0 AND NOT EXISTS(SELECT * FROM TRANSACTION_LINE Z1 WHERE Z1.ITEM_ID = Z.ITEM_ID AND Z1.CATEGORY = '14' AND Z1.STOCK_ACTION = -1 AND Z1.INVENTORY_QTY > 0 AND Z.TRANSACTION_DATE < Z1.TRANSACTION_DATE)";
            }
                
                // textBox9.Text = strSql;
                try
                {
                    Double dwcb = 0.00;
                    sdr1 = db.GetDataReader1(strSql);
                    while (sdr1.Read())
                    {
                        //Double bjtr = Convert.ToDouble(sdr1["本阶材料"].ToString()) + Convert.ToDouble(sdr1["本阶人工"].ToString()) + Convert.ToDouble(sdr1["本阶制费"].ToString()) + Convert.ToDouble(sdr1["本阶加工费"].ToString());

                        dwcb = Convert.ToDouble(sdr1["单位成本"].ToString());
                        gridView9.SetRowCellValue(i, "实际", dwcb.ToString("0.##"));
                        //gridView9.SetRowCellValue(i, "品名", sdr1["品名"].ToString());
                        //gridView9.SetRowCellValue(i, "规格", sdr1["规格"].ToString());
                    }
                    if(sdr1.HasRows)
                    {
                        
                    }
                    else
                    {
                        gridView9.SetRowCellValue(i, "实际", 0.ToString("0.##"));
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    throw ex;
                }
                finally
                {
                    sdr1.Close();
                }
            }
                progressBar4.Visible = false;
            
            for (int i = 0; i <= gridView9.RowCount - 1; i++)
            {
                try
                {
                    Double bja = Convert.ToDouble(gridView9.GetRowCellValue(i, "标准"));
                    Double bjb = Convert.ToDouble(gridView9.GetRowCellValue(i, "实际"));
                    Double bjc = Math.Round((bja - bjb), 2);
                    gridView9.SetRowCellValue(i, "差异", bjc);
                }
                catch
                {
                }
            }
        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private void button20_Click(object sender, EventArgs e)
        {
            string strSql;
            strSql = "SELECT DOC_NO 工单号,ITEM.ITEM_CODE 产品品号,ITEM.ITEM_NAME 产品品名,ITEM.ITEM_SPECIFICATION 产品规格,ITEM.DRAWING_NO 产品图号,CAST(PLAN_QTY AS REAL) 预计产量,CAST(COMPLETED_QTY AS REAL) 已入库量,MO.REMARK 备注 FROM MO LEFT JOIN ITEM ON MO.ITEM_ID=ITEM.ITEM_BUSINESS_ID WHERE ACTUAL_COMPLETE_DATE>='" + dateTimePicker3.Value.ToShortDateString() + "' AND ACTUAL_COMPLETE_DATE<='" + dateTimePicker4.Value.ToShortDateString() + "' AND ITEM.ITEM_CODE LIKE '%" + textBox12.Text + "%' AND (ITEM.ITEM_NAME LIKE '%" + textBox10.Text + "%' OR ITEM.ITEM_SPECIFICATION LIKE '%" + textBox10.Text + "%' OR ITEM.DRAWING_NO LIKE '%" + textBox10.Text + "%') AND CAST(COMPLETED_QTY AS REAL)>0";
            //textBox1.Text = strSql;
            //return;
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl11.DataSource = dt;
            }
            catch
            {

            }
        }

        private void gridView1_FocusedRowChanged_1(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {
            string strSql;
            try
            {
            String gddh = gridView11.GetFocusedRowCellValue("工单号").ToString();
            strSql = @"SELECT ITEM.ITEM_CODE 材料品号,ITEM.ITEM_NAME 材料品名,ITEM.ITEM_SPECIFICATION 材料规格,ITEM.DRAWING_NO 材料图号,CAST(SUM(A.COST_AMT*A.STOCK_ACTION*(-1.00)) AS REAL) 领用金额,CAST(SUM(ISSUE_RECEIPT_QTY*A.STOCK_ACTION*(-1.00)) AS REAL) 领用数量,CAST(SUM(A.CLJE*A.STOCK_ACTION*(-1.00)) AS REAL) 材料,CAST(SUM(A.RGJE*A.STOCK_ACTION*(-1.00)) AS REAL) 人工,CAST(SUM(A.ZFJE*A.STOCK_ACTION*(-1.00)) AS REAL) 制费,CAST(SUM(A.ZFJE*A.STOCK_ACTION*(-1.00)) AS REAL) 加工费 FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN ITEM ON ITEM.ITEM_BUSINESS_ID=ISSUE_RECEIPT_D.ITEM_ID LEFT JOIN MO ON MO.MO_ID=ISSUE_RECEIPT_D.MO_ID
                       LEFT JOIN(SELECT TRANSACTION_LINE.SOURCE_ID_ROid, COST_AMT, STOCK_ACTION, (SELECT ISNULL(SUM(COST_AMT),0) FROM TRANS_LINE_COST_DETAIL LEFT JOIN COST_ELEMENT ON TRANS_LINE_COST_DETAIL.COST_ELEMENT_ID = COST_ELEMENT.COST_ELEMENT_ID WHERE COST_ELEMENT.COST_ELEMENT_CATEGORY = '1' AND TRANSACTION_LINE.TRANSACTION_LINE_ID = TRANS_LINE_COST_DETAIL.TRANSACTION_LINE_ID) CLJE,
                       (SELECT ISNULL(SUM(COST_AMT), 0) FROM TRANS_LINE_COST_DETAIL LEFT JOIN COST_ELEMENT ON TRANS_LINE_COST_DETAIL.COST_ELEMENT_ID = COST_ELEMENT.COST_ELEMENT_ID WHERE COST_ELEMENT.COST_ELEMENT_CATEGORY = '2' AND TRANSACTION_LINE.TRANSACTION_LINE_ID = TRANS_LINE_COST_DETAIL.TRANSACTION_LINE_ID) RGJE,
                       (SELECT ISNULL(SUM(COST_AMT), 0) FROM TRANS_LINE_COST_DETAIL LEFT JOIN COST_ELEMENT ON TRANS_LINE_COST_DETAIL.COST_ELEMENT_ID = COST_ELEMENT.COST_ELEMENT_ID WHERE COST_ELEMENT.COST_ELEMENT_CATEGORY = '3' AND TRANSACTION_LINE.TRANSACTION_LINE_ID = TRANS_LINE_COST_DETAIL.TRANSACTION_LINE_ID) ZFJE,
                       (SELECT ISNULL(SUM(COST_AMT), 0) FROM TRANS_LINE_COST_DETAIL LEFT JOIN COST_ELEMENT ON TRANS_LINE_COST_DETAIL.COST_ELEMENT_ID = COST_ELEMENT.COST_ELEMENT_ID WHERE COST_ELEMENT.COST_ELEMENT_CATEGORY = '4' AND TRANSACTION_LINE.TRANSACTION_LINE_ID = TRANS_LINE_COST_DETAIL.TRANSACTION_LINE_ID) JGJE
                       FROM TRANSACTION_LINE WHERE SOURCE_ID_RTK = 'ISSUE_RECEIPT.ISSUE_RECEIPT_D' AND TRANSACTION_LINE.TRANSACTION_DATE >= '2023-01-01') A ON A.SOURCE_ID_ROid = ISSUE_RECEIPT_D.ISSUE_RECEIPT_D_ID WHERE MO.DOC_NO = '" + gddh + "'   GROUP BY ITEM.ITEM_CODE,ITEM.ITEM_NAME,ITEM.ITEM_SPECIFICATION,ITEM.DRAWING_NO " +
                       "UNION ALL SELECT COST_ELEMENT_CODE,COST_ELEMENT_NAME,COST_DOC.ACCOUNT_YEAR+'-'+ACCOUNT_PERIOD_CODE,'',0,0,0,CAST(INPUT_CLCOST_AMT AS REAL),0,0 FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC.COST_DOC_ID=COST_DOC_D.COST_DOC_ID LEFT JOIN MO ON COST_DOC.MO_ID=MO.MO_ID LEFT JOIN COST_ELEMENT ON COST_DOC_D.COST_ELEMENT_ID=COST_ELEMENT.COST_ELEMENT_ID WHERE MO.DOC_NO='" + gddh + "' AND COST_ELEMENT_CATEGORY='2' " +
                       "UNION ALL SELECT COST_ELEMENT_CODE,COST_ELEMENT_NAME,COST_DOC.ACCOUNT_YEAR+'-'+ACCOUNT_PERIOD_CODE,'',0,0,0,0,CAST(INPUT_CLCOST_AMT AS REAL),0 FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC.COST_DOC_ID=COST_DOC_D.COST_DOC_ID LEFT JOIN MO ON COST_DOC.MO_ID=MO.MO_ID LEFT JOIN COST_ELEMENT ON COST_DOC_D.COST_ELEMENT_ID=COST_ELEMENT.COST_ELEMENT_ID WHERE MO.DOC_NO='" + gddh + "' AND COST_ELEMENT_CATEGORY='3' " +
                       "UNION ALL SELECT COST_ELEMENT_CODE,COST_ELEMENT_NAME,COST_DOC.ACCOUNT_YEAR+'-'+ACCOUNT_PERIOD_CODE,'',0,0,0,0,0,CAST(INPUT_CLCOST_AMT AS REAL) FROM COST_DOC_D LEFT JOIN COST_DOC ON COST_DOC.COST_DOC_ID=COST_DOC_D.COST_DOC_ID LEFT JOIN MO ON COST_DOC.MO_ID=MO.MO_ID LEFT JOIN COST_ELEMENT ON COST_DOC_D.COST_ELEMENT_ID=COST_ELEMENT.COST_ELEMENT_ID WHERE MO.DOC_NO='" + gddh + "' AND COST_ELEMENT_CATEGORY='4'";
            //textBox1.Text = strSql;
            //return;
          
                DataTable dt;
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl12.DataSource = dt;
            }
            catch
            {

            }
        }

        private void button18_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl11.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button19_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl12.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}
